从csv文件中导入数据到MySQL数据库

港控/mmm° 2022-12-27 01:24 272阅读 0赞

> 从csv文件中导入数据到MySQL数据库
> 
> 转:
> 
> 一、Workbench客户端导入（8.0.11基本导不全且速度奇慢）
> 
> 1.点击如下图标创建数据库（非必要）
> 
>  
> 
> ![20191225125006641.png][]
> 
> 2.在表类上右键导入
> 
> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYWhlMTIyNA_size_16_color_FFFFFF_t_70][]
> 
>  
> 
> 二、SQL语句导入（可先用上步导入，再清除数据，这样可免去设置格式等问题）
> 
> 基本语法：
> 
>  
> 
> load data \[low\_priority | concurrent\]  \[local\]  infile  '*file\_name*'
> 
> \[replace | ignore\]
> 
> INTO TABLE *tbl\_name*
> 
> \[partition (*partition\_name *\[, *partition\_name*\] ...)\] \[character set *charset\_name*\]
> 
> \[ **\{**  fielsd | columns **\}**
> 
> \[terminated by '*string*'\]
> 
> \[\[optionally\]  enclosed by '*char*'\]
> 
> \[escaped by '*char*'\] \]
> 
> \[LINES
> 
> \[STARTING BY '*string*'\]
> 
> \[TERMINATED BY '*string*'\]
> 
> \]
> 
> \[ignore *number * **\{** LINES | ROWS**\}** \]
> 
> \[(*col\_name\_or\_user\_var *\[, *col\_name\_or\_user\_var*\] ...)\]
> 
> \[SET *col\_name*=**\{** *expr *| DEFAULT**\}**, \[, *col\_name*=**\{** *expr *| DEFAULT**\}**\] ...\]
> 
>  
> 
> **解释：**
> 
> **replace**和**ignore**关键词控制对现有的唯一键记录的重复的处理。如果你指定**replace**，新行将代替有相同的唯一键值的现有行。如果你指定**ignore**，跳过有唯一键的现有行的重复行的输入。如果你不指定任何一个选项，当找到重复键时，出现一个错误，并且文本文件的余下部分被忽略。 
> 
>  
> 
> 如果你指定关键词low\_priority，那么MySQL将会等到没有其他人读这个表的时候，才插入数据。如果指定local关键词，则表明从客户主机读文件。如果local没指定，文件必须位于服务器上。
> 
>  
> 
> **文件名必须以文字字符串形式给出。在****Windows上，将路径名中的反斜杠指定为正斜杠或加倍反斜杠。**
> 
>  
> 
> 如果指定FIELDS子句，则每个子子句（TERMINATED BY，\[OPTIONALLY\] ENCLOSED BY和ESCAPED BY）也是可选的，但您必须至少指定其中一个子句。允许这些子句的参数仅包含ASCII字符。
> 
>  
> 
> LOAD DATA INFILE可用于读取从外部源获取的文件。例如，许多程序可以以逗号分隔值（CSV）格式导出数据，这样行的字段用逗号分隔并用双引号括起来，并带有一行初始的列名。如果此类文件中的行由回车符/换行符对终止，则此处显示的语句说明了用于加载文件的字段和行处理选项：
> 
> LOAD DATA INFILE 'data.txt' INTO TABLE *tbl\_name*
> 
> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
> 
> LINES TERMINATED BY '\\r\\n'
> 
> IGNORE 1 LINES;
> 
> 如果输入值不一定用***引号*** 括起来，请在ENCLOSED BY关键字之前使用OPTIONALLY。
> 
>  
> 
> 如果指定OPTIONALLY，则ENCLOSED BY字符仅用于包含具有字符串数据类型的列
> 
>  
> 
> **示例****1****：**
> 
> **load data infile **'F:/Data/test1.csv' --CSV文件存放路径
> 
> **into table** student character **set **utf8 --要将数据导入的表名
> 
> **fields**
> 
> **terminated by **',' --指出数据之间的分隔符；
> 
> **optionally enclosed by **'"' --指出字段以半角*双引号* 括住，如果不一定括住，就加上optionally；
> 
> **escaped by **'"'--指出转义字符，字符串本身的双引号用两个双引号表示（CSV中，对双引号的转义是两个双引号，即""，而不是\\"），如果为空则是不设置转义字符，可以防止文本中的“\\”被当作转义符;
> 
> **lines terminated by **'\\r\\n'--数据行之间以\\r\\n分隔，如果csv文件是在windows下生成，那分割用 '\\r\\n'，linux下用 '\\n'。
> 
> **ignore 1 lines**
> 
> **(Id,@dummy,DayOfWeek,PdDistrict,Address,X,Y);**
> 
> /\*忽略第一行，因为第一行往往是字段名。后边括号中有个字段很特别 @dummy，它是说如果csv文件中有个字段我不想插进去，那就把对应字段名变成@dummy \*/
> 
>  
> 
> 示例2：
> 
> load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/zuieniang2.csv"
> 
> into table zuieniang2  character set utf8
> 
> Fields Terminated By ','
> 
> optionally Enclosed By '"'
> 
> escaped by ''
> 
> Lines Terminated By '\\r\\n'
> 
> IGNORE 1 LINES;
> 
>  
> 
>  
> 
>  
> 
> fields terminated by '': 这是指出csv文件中的字段终止符，也就是数据之间的分隔符；
> 
> enclosed by '"': 指出字符串以半角双引号包住；字符串本身的双引号用两个双引号表示 
> 
> lines terminated by '': 数据行之间以\\r\\n分隔
> 
>  
> 
>  
> 
> 三、可能碰到的错误：
> 
> 1.如果发生1290或29错误，原因是导入文件所在目录不是安全目录。 执行show variables like '%secure%';，查看MYSQL指定的安全目录在哪里。将需要导入的文件放在这个目录下面即可。
> 
> 如果还不行，在my.ini文件中搜索secure-file-priv找到并将它的值改为
> 
> secure-file-priv=''
> 
> 重启MySQL服务（不是客户端）就可以在任何目录下操作了（如果重启MySQL不行就重启电脑）。
> 
> 2.workbench发生2013错误或黄色叹号
> 
> 在view-Preferences里修改如下时间设置后重启客户端：
> 
>  
> 
> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYWhlMTIyNA_size_16_color_FFFFFF_t_70 1][]
> 
> This sets the maximum amount of time (in seconds) that a query can take before MySQL Workbench disconnects from the MySQL server.
> 
> 要是还不行那只能写SQL语句了。不得不说workbench就是个垃圾，经常莫名其妙出错，强烈建议写语句。
> 
>  
> 
> 3.Incorrect integer value （不能读入空值）
> 
> MySQL 5.0以上的版本如果是空值应该要写NULL，要在安装mysql的时候去除默认勾选的enable strict SQL mode。那么如果我们安装好了mysql怎么办了，解决办法是更改mysql中的配置 my.ini
> 
> （1）5.x版本
> 
> my.ini中查找sql-mode，
> 
> 默认为sql-mode="STRICT\_TRANS\_TABLES,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION"，
> 
> 将其修改为sql-mode="NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION"，重启mysql后即可
> 
> （2）8.x版本
> 
> 改为
> 
> sql-mode="NO\_ENGINE\_SUBSTITUTION"
> 
>  
> 
> 4. 3719 'utf8' is currently an alias for the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
> 
> 在my.ini中配置default-character-set的值为：
> 
> default-character-set=utf8mb4
> 
>  
> 
> 四、导出命令：
> 
> select \* from 表名
> 
> into outfile '导出路径\\\\test.csv'
> 
> fields terminated by ','
> 
> enclosed by '"'
> 
> escaped by ''
> 
> lines terminated by '\\r\\n';
> 
>  
> 
> 如果要导出带列名的，可用：
> 
> select \* into outfile 'd:\\\\columns.csv'
> 
> fields terminated by ','
> 
> lines terminated by '\\n'
> 
> from (
> 
> select 'name','age'
> 
> union
> 
> select name,age from test) AS t;
> 
>  
> 
>  
> 
> 如果导出后出现某些列的总行数不一致，很有可能是某一列某一行含回车或换行符，如果表的某个列里包含回车符或者换行符，那么生成的**CSV**文件或者进行**excel**导入，都会将原本的1行数据，拆分成2行。因为**CSV**或者**excel**导入，是按数据的行来认定数据条数。导出前可先去除：
> 
> **UPDATE ****tablename**** SET  ****field**** = REPLACE(REPLACE(****field****, CHAR(10),****' '****), CHAR(13),****' '****);**
> 
> **\# ****char(10):  换行符，char(13):  回车符**
> 
> 举例：
> 
> UPDATE zuieniang2 SET 认证原因 = REPLACE(REPLACE(认证原因, CHAR(10),' '), CHAR(13),' ');
> 
> 注：
> 
> 如果执行上句时出现1175错误，这是因为MySql运行在safe-updates模式下，该模式会导致非主键下无法执行update或者delete命令。可执行下句后再执行update：
> 
> SET SQL\_SAFE\_UPDATES = 0;
> 
>  
> 
>  
> 
> （1）查询已有数据库，使用命令（注意结尾的分号）
> 
> **show databases**;
> 
> （2）使用这个数据库，使用命令
> 
> **use test**;
> 
> （3）查询数据库中的表，使用命令
> 
> **show tables**;
> 
>  
> 
>  
> 
> **如果查询的两个表大小相当，那么用in和exists差别不大**。 
> 
> 如果两个表中一个较小，一个是大表，则子查询表大的用exists（长~大），子查询表小的用in（短~小）： 
> 
> 例如：表A（小麦），表B（粮食）
> 
> 1：
> 
> select \* from A where cc in (select cc from B) 效率低，用到了A表上cc列的索引；
> 
> select \* from A where exists (select cc from B where cc=A.cc) 效率高，用到了B表上cc列的索引。 
> 
> 2：
> 
> select \* from B where cc in (select cc from A) 效率高，用到了B表上cc列的索引；
> 
> select \* from B where exists (select cc from A where cc=B.cc) 效率低，用到了A表上cc列的索引。
> 
>  
> 
> not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。**所以无论那个表大，用not exists都比not in要快**。
> 
>

[20191225125006641.png]: /images/20221120/a191b89a36bb410d9d1fac59681c8e1e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYWhlMTIyNA_size_16_color_FFFFFF_t_70]: /images/20221120/b4f6492dfbb943faa2856d5a1525a1e3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYWhlMTIyNA_size_16_color_FFFFFF_t_70 1]: /images/20221120/80cb75abf7ac4782b425e0e24daf1a93.png