sql插入数据的方法以及效率

最近的sql作业是在一个已经有10000条数据的table里插入1，000,000条数据。插入效率在不同的机子下差距很大= = 在此只纵向对比一下几种插入过程的区别和注意事项

1. 直接 load data infile

基本语句：

load data  [low_priority] [local] infile 'file_name txt' [replace | ignore]
into table tbl_name
[fields
[terminated by't']
[OPTIONALLY] enclosed by '']
[escaped by'\' ]]
[lines terminated by'n']
[ignore number lines]
[(col_name,   )]
 

例子：

load data infile `path.txt` into table table_name fields terminated by ',' lines terminated by '\r\n';

注意事项：1. 文件名使用1左边那个键引起的，而terminated by使用单引号引起的

    2. windows中行的分隔符是\r\n 而unix下是\n

2.将所有数据合成一个insert语句，导入sql后缀的脚本

注意事项：防止mysql因为语句过大而丢失链接。修改mysql的最大语句限制，详见http://smilemonkey.iteye.com/blog/660889

1）mysql 导入数据

   mysql  数据库名 -u 用户名 -p 密码< 文件名

      如：mysql -u root -p admin < test.sql (如果sql路径不是当前路径，改成绝对路径 mysql -u root -p admin < /home/root/test.sql)

2） source 导入数据

     source  /home/root/XX.sql   只能用mysql -uroot -padmin 登录后才能使用

      如： source  /home/root/test.sql

 

顺便记录一下

3） mysqldump 导出数据

      a. 只导出表结构

          mysqldump --opt  -d  数据库名 -u 用户名 -p 密码> xx.sql

          如： mysqldump --opt -d shop_data -u root  -p admin > onlystruct_shop_data.sql

      b. 只导出数据

          mysqldump -t  数据库名 -u 用户名-p 密码 > xx.sql

          如： mysqldump --t shop_data -u root  -p admin > onlydata_shop_data.sql

      c. 导出所有数据和结构

          mysqldump 数据库 -u 用户名 -p 密码 > xx.sql

          如： mysqldump shop_data -u root -p admin > shop_data.sql

      d. 导出特定表

          mysqldump  数库 -u 用户名 -p 密码  -B  --table 表名 > xx.sql

          如： mysqldump shop_data -u root -p admin -B --table user > user.sql

 

3. insert语句一条一条地插入数据，非常地慢。

根据实际试验，效率2>1>3。在同学的mac下方法1已经非常快了。

但在我的电脑下2的效率比1好。

数据测试详见：

http://blog.jobbole.com/48417/

******另外，这次做sql作业，一个很容易犯的错误就是把table或者column的名字起成了sql的保留字。这样，如果用的时候不用1左边那个键把它引起来，就会报错。不过注意还是不要用保留字吧。