SQL如何导入CSV文件数据？

常见场景与核心概念

1. 数据导入：将文件内容加载到数据库表中（如CSV导入）。
2. 文件读取：直接读取文件内容（如文本解析）。
3. 权限要求：数据库服务需有文件系统的访问权限（关键安全配置）。

不同数据库的操作方法

MySQL / MariaDB

• 导入CSV到表（LOAD DATA INFILE）：

  LOAD DATA INFILE '/path/to/file.csv'
  INTO TABLE your_table
  FIELDS TERMINATED BY ','  -- 分隔符
  ENCLOSED BY '"'           -- 文本引号
  LINES TERMINATED BY '\n'  -- 换行符
  IGNORE 1 ROWS;            -- 跳过标题行

  • 权限配置：需在my.cnf中设置 secure_file_priv 参数允许文件路径。

• 读取文件内容（LOAD_FILE()）：

  SELECT LOAD_FILE('/path/to/file.txt') AS file_content;

  • 要求：文件需在数据库服务器本地，且用户有FILE权限。

SQL Server

• 导入CSV（BULK INSERT）：

  BULK INSERT your_table
  FROM 'C:\path\to\file.csv'
  WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2  -- 跳过标题行
  );

  • 权限：需授予数据库服务账户文件读取权限。

• 通过OPENROWSET读取文件：

  SELECT * FROM OPENROWSET(
    BULK 'C:\path\to\file.csv',
    SINGLE_CLOB  -- 以文本形式读取
  ) AS file_data;

PostgreSQL

• 导入CSV（COPY命令）：

  COPY your_table FROM '/path/to/file.csv'
  DELIMITER ','
  CSV HEADER;  -- 包含标题行

  • 权限：需以postgres用户运行，或授予数据库角色文件访问权（pg_read_server_files）。

• 读取文件（pg_read_file）：

  SELECT pg_read_file('/path/to/file.txt');

  仅限超级用户使用。

Oracle

• 外部表（推荐）：将文件映射为虚拟表。

  CREATE TABLE ext_table (
    col1 VARCHAR2(50),
    col2 NUMBER
  ) ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_dir  -- 预先创建的目录对象
    ACCESS PARAMETERS (
      RECORDS DELIMITED BY NEWLINE
      FIELDS TERMINATED BY ','
    )
    LOCATION ('file.csv')
  );

  • 目录配置：需先创建目录对象并授权：

    CREATE DIRECTORY data_dir AS '/path/to/files/';
    GRANT READ ON DIRECTORY data_dir TO your_user;

• 直接读取（UTL_FILE包）：

  DECLARE
    file_handle UTL_FILE.FILE_TYPE;
    file_content VARCHAR2(4000);
  BEGIN
    file_handle := UTL_FILE.FOPEN('DATA_DIR', 'file.txt', 'R');
    UTL_FILE.GET_LINE(file_handle, file_content);
    DBMS_OUTPUT.PUT_LINE(file_content);
    UTL_FILE.FCLOSE(file_handle);
  END;

通用注意事项

1. 文件路径问题：
   • 使用绝对路径,避免相对路径歧义。
   • 确保路径在数据库服务器本地（网络路径需特殊配置）。
2. 权限管理：
   • 数据库服务账户需有文件/目录的读写权限。
   • 生产环境避免使用超级用户操作文件。
3. 安全风险：
   • 禁用未经验证的文件上传（防SQL注入）。
   • 限制LOAD_FILE等函数的使用范围。
4. 格式兼容性：
   • 检查文件编码（如UTF-8）、换行符（Windows/Linux差异）。
   • 处理特殊字符（如转义引号）。

错误排查

• 权限拒绝：检查数据库服务账户的OS级文件权限。
• 文件不存在：确认路径在数据库服务器本地（非客户端机器）。
• 语法错误：分隔符需与文件实际格式匹配。
• 内存限制：大文件需分批次导入（如BATCHSIZE参数）。

引用说明：本文操作基于官方文档整理，具体细节请参考：

• MySQL： LOAD DATA INFILE Syntax
• SQL Server：BULK INSERT Documentation
• PostgreSQL：COPY Command
• Oracle：UTL_FILE Package