mysql之load data

admin

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'    [REPLACE | IGNORE] INTO TABLE tbl_name    [CHARACTER SET charset_name]  [{FIELDS | COLUMNS} [TERMINATED BY 'string']  [[OPTIONALLY] ENCLOSED BY 'char']    [ESCAPED BY 'char'] ]  [LINES [STARTING BY 'string']    [TERMINATED BY 'string'] ]    [IGNORE number LINES]    [(col_name_or_user_var,...)]    [SET col_name = expr,...]
LOW_PRIORITY 设置对于表级锁的存储引擎会导致load data需要在没有任何其他客户端在访问这张表时才会被执行   CONCURRENT   设置对于满足高并发(不包含空块)的myisam表执行load data时，其他线程可以检索这个表的数据
==默认不做设置==

TERMINATED BY 将字段、行之间的间隔符进行过滤   ENCLOSED BY   将字符两端的引号字符过滤掉   ESCAPED BY    转义

LINES STARTING BY  可以将每行包含特定字符的记录过滤后将特定字符后面的字符插入数据库   IGNORE umber LINES 可以设置将文件前多少行忽略   SET col_name=expr   可以将文本中未指定默认值的字段进行赋值
注：
1、mysql里面用“\”（反斜干）对字符进行转义，表达一个真实的“\”需要写成“\\”
2、ENCLOSED BY、ESCAPED BY后面的值只能是单个的字符，TERMINATED BY、LINES STARTING BY和LINES TERMINATED BY后面可以跟多个字符


关于mysql的远程load data参考陈旭的这篇blog总结的很好     http://orzdba.wordpress.com/2011/03/17/mysql_load-data/


另外关于load data还有几个小细节以及实验过程如下：
--local-infile选项
  0 启动mysqld从服务器端禁用所有LOAD DATA LOCAL命令；1  启用LOAD DATA LOCA

如果LOAD DATA LOCAL INFILE在服务器或客户端被禁用，试图执行该语句的客户端将收到下面的错误消息：
ERROR 1148: The used command is not allowed with this MySQL version

（出现以上报错的时候就要检查下是否设置了--local-infile=1了，默认是0的）

1、在需要远程导入数据的主机上授权用户：
[MM-Writable@v031014.sqa.cm4 ～]
grant select,insert,update,delete on test.* to 'qianwei'@'%' identified by 'qianwei';
(远程登录的时候必须要有密码的帐号)

2、测试表结构如下：
[MM-Writable@v031014.sqa.cm4 ～]
root@test 12:01:06>show create table tmp_qw_test\G
*************************** 1. row ***************************
       Table: tmp_qw_test
Create Table: CREATE TABLE `tmp_qw_test` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `user_id` bigint(20) NOT NULL,
  `target_id` bigint(20) NOT NULL,
  `group_id` bigint(20) NOT NULL,
  `common_friend_count` int(11) NOT NULL,
  `common_friends` varchar(512) NOT NULL,
  `gmt_create` datetime NOT NULL,
  `gmt_modified` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

root@test 12:09:00>select * from tmp_qw_test;
Empty set (0.00 sec)

3、数据文件格式如下：
[MM-Writable@dev031033.sqa.cm4 /tmp]
$more tmp_qw_test.txt
"2011-04-03 00:00:00",100001536,34806598,11217864,1,"103924803"
"2011-04-03 00:00:00",100001536,34806598,204886714,1,"103924803"
"2011-04-03 00:00:00",100001536,34806598,48259232,1,"103924803"
"2011-04-03 00:00:00",100001536,34806598,201869594,1,"103924803"
"2011-04-03 00:00:00",100001536,34806598,76686624,1,"103924803"
"2011-04-03 00:00:00",100001536,34806598,279932430,1,"103924803"
"2011-04-03 00:00:00",100001536,34806598,54773280,1,"103924803"
"2011-04-03 00:00:00",100001536,34806598,448342406,1,"103924803"
"2011-04-03 00:00:00",100001536,34806598,362098910,1,"103924803"
"2011-04-03 00:00:00",100001536,34806598,196823514,1,"103924803"

对应的字段为：gmt_create,user_id,group_id,f_friend_id,commfriends,commfriends_list

4、远程导数据命令：
[MM-Writable@dev031033.sqa.cm4 /tmp]
$mysql -uqianwei -pqianwei test -h10.232.31.14  --local-infile=1 --show-warnings -v -v -v -e "load data LOCAL infile '/tmp/tmp_qw_test.txt'  REPLACE into table tmp_qw_test fields terminated by ',' ENCLOSED BY '\"' (gmt_create,user_id,group_id,target_id,common_friend_count,common_friends)";
--------------
load data LOCAL infile '/tmp/tmp_qw_test.txt'  REPLACE into table tmp_qw_test fields terminated by ',' ENCLOSED BY '"' (gmt_create,user_id,group_id,target_id,common_friend_count,common_friends)
--------------

Query OK, 10 rows affected, 1 warning (0.00 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

Warning (Code 1364): Field 'gmt_modified' doesn't have a default value
Bye

因为字段不是完全匹配，gmt_modified 字段表结构要求非空。此处会报warning，分别测试了使用replace、ignore或者忽略参数三种情况数据均能正常插入

[MM-Writable@v031014.sqa.cm4 ～]
root@test 12:09:09>select * from tmp_qw_test;
+----+-----------+-----------+----------+---------------------+----------------+---------------------+---------------------+
| id | user_id   | target_id | group_id | common_friend_count | common_friends | gmt_create          | gmt_modified        |
+----+-----------+-----------+----------+---------------------+----------------+---------------------+---------------------+
|  1 | 100001536 |  11217864 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 0000-00-00 00:00:00 |
|  2 | 100001536 | 204886714 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 0000-00-00 00:00:00 |
|  3 | 100001536 |  48259232 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 0000-00-00 00:00:00 |
|  4 | 100001536 | 201869594 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 0000-00-00 00:00:00 |
|  5 | 100001536 |  76686624 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 0000-00-00 00:00:00 |
|  6 | 100001536 | 279932430 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 0000-00-00 00:00:00 |
|  7 | 100001536 |  54773280 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 0000-00-00 00:00:00 |
|  8 | 100001536 | 448342406 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 0000-00-00 00:00:00 |
|  9 | 100001536 | 362098910 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 0000-00-00 00:00:00 |
| 10 | 100001536 | 196823514 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 0000-00-00 00:00:00 |
+----+-----------+-----------+----------+---------------------+----------------+---------------------+---------------------+
10 rows in set (0.00 sec)

5、文件中的双引号不进行过滤时，时间字段会被截断变为0：
[MM-Writable@dev031033.sqa.cm4 /tmp]
$mysql -uqianwei -pqianwei test -h10.232.31.14  --local-infile=1 --show-warnings -v -v -v -e "load data LOCAL infile '/tmp/tmp_qw_test.txt'  REPLACE into table tmp_qw_test fields terminated by ','  (gmt_create,user_id,group_id,target_id,common_friend_count,common_friends)";
--------------
load data LOCAL infile '/tmp/tmp_qw_test.txt'  REPLACE into table tmp_qw_test fields terminated by ','  (gmt_create,user_id,group_id,target_id,common_friend_count,common_friends)
--------------

Query OK, 10 rows affected, 11 warnings (0.12 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 10

Warning (Code 1364): Field 'gmt_modified' doesn't have a default value
Warning (Code 1264): Out of range value adjusted for column 'gmt_create' at row 1
Warning (Code 1264): Out of range value adjusted for column 'gmt_create' at row 2
Warning (Code 1264): Out of range value adjusted for column 'gmt_create' at row 3
Warning (Code 1264): Out of range value adjusted for column 'gmt_create' at row 4
Warning (Code 1264): Out of range value adjusted for column 'gmt_create' at row 5
Warning (Code 1264): Out of range value adjusted for column 'gmt_create' at row 6
Warning (Code 1264): Out of range value adjusted for column 'gmt_create' at row 7
Warning (Code 1264): Out of range value adjusted for column 'gmt_create' at row 8
Warning (Code 1264): Out of range value adjusted for column 'gmt_create' at row 9
Warning (Code 1264): Out of range value adjusted for column 'gmt_create' at row 10
Bye

表数据：
[MM-Writable@v031014.sqa.cm4 ～]
root@test 12:13:59>select * from tmp_qw_test;
+----+-----------+-----------+----------+---------------------+----------------+---------------------+---------------------+
| id | user_id   | target_id | group_id | common_friend_count | common_friends | gmt_create          | gmt_modified        |
+----+-----------+-----------+----------+---------------------+----------------+---------------------+---------------------+
|  1 | 100001536 |  11217864 | 34806598 |                   1 | "103924803"    | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
|  2 | 100001536 | 204886714 | 34806598 |                   1 | "103924803"    | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
|  3 | 100001536 |  48259232 | 34806598 |                   1 | "103924803"    | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
|  4 | 100001536 | 201869594 | 34806598 |                   1 | "103924803"    | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
|  5 | 100001536 |  76686624 | 34806598 |                   1 | "103924803"    | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
|  6 | 100001536 | 279932430 | 34806598 |                   1 | "103924803"    | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
|  7 | 100001536 |  54773280 | 34806598 |                   1 | "103924803"    | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
|  8 | 100001536 | 448342406 | 34806598 |                   1 | "103924803"    | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
|  9 | 100001536 | 362098910 | 34806598 |                   1 | "103924803"    | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 10 | 100001536 | 196823514 | 34806598 |                   1 | "103924803"    | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+----+-----------+-----------+----------+---------------------+----------------+---------------------+---------------------+
10 rows in set (0.01 sec)

6、设置gmt_modified字段取值和gmt_create一致：
[MM-Writable@dev031033.sqa.cm4 /tmp]
$mysql -uqianwei -pqianwei test -h10.232.31.14  --local-infile=1 --show-warnings -v -v -v -e "load data LOCAL infile '/tmp/tmp_qw_test.txt'  REPLACE into table tmp_qw_test fields terminated by ',' ENCLOSED BY '\"' (gmt_create,user_id,group_id,target_id,common_friend_count,common_friends) set gmt_modified=gmt_create";
--------------
load data LOCAL infile '/tmp/tmp_qw_test.txt'  REPLACE into table tmp_qw_test fields terminated by ',' ENCLOSED BY '"' (gmt_create,user_id,group_id,target_id,common_friend_count,common_friends) set gmt_modified=gmt_create
--------------

Query OK, 10 rows affected (0.00 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

Bye

[MM-Writable@v031014.sqa.cm4 ～]
root@test 12:19:01>select * from tmp_qw_test;
+----+-----------+-----------+----------+---------------------+----------------+---------------------+---------------------+
| id | user_id   | target_id | group_id | common_friend_count | common_friends | gmt_create          | gmt_modified        |
+----+-----------+-----------+----------+---------------------+----------------+---------------------+---------------------+
|  1 | 100001536 |  11217864 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 2011-04-03 00:00:00 |
|  2 | 100001536 | 204886714 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 2011-04-03 00:00:00 |
|  3 | 100001536 |  48259232 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 2011-04-03 00:00:00 |
|  4 | 100001536 | 201869594 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 2011-04-03 00:00:00 |
|  5 | 100001536 |  76686624 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 2011-04-03 00:00:00 |
|  6 | 100001536 | 279932430 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 2011-04-03 00:00:00 |
|  7 | 100001536 |  54773280 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 2011-04-03 00:00:00 |
|  8 | 100001536 | 448342406 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 2011-04-03 00:00:00 |
|  9 | 100001536 | 362098910 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 2011-04-03 00:00:00 |
| 10 | 100001536 | 196823514 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 2011-04-03 00:00:00 |
+----+-----------+-----------+----------+---------------------+----------------+---------------------+---------------------+
10 rows in set (0.00 sec)

7、使用select...into outfile导出数据文件，此时使用的是tab键作为分隔符。导入时不需要进行符号相关处理
[MM-Writable@v031014.sqa.cm4 ～]
root@test 12:35:52>select * into outfile '/tmp/tmp_qw_test1.sql' from tmp_qw_test;
Query OK, 10 rows affected (0.04 sec)

[MM-Writable@v031014.sqa.cm4 /tmp]
$more tmp_qw_test1.sql
1 100001536 11217864 34806598 1 103924803 2011-04-03 00:00:00 2011-04-03 00:00:00
2 100001536 204886714 34806598 1 103924803 2011-04-03 00:00:00 2011-04-03 00:00:00
3 100001536 48259232 34806598 1 103924803 2011-04-03 00:00:00 2011-04-03 00:00:00
4 100001536 201869594 34806598 1 103924803 2011-04-03 00:00:00 2011-04-03 00:00:00
5 100001536 76686624 34806598 1 103924803 2011-04-03 00:00:00 2011-04-03 00:00:00
6 100001536 279932430 34806598 1 103924803 2011-04-03 00:00:00 2011-04-03 00:00:00
7 100001536 54773280 34806598 1 103924803 2011-04-03 00:00:00 2011-04-03 00:00:00
8 100001536 448342406 34806598 1 103924803 2011-04-03 00:00:00 2011-04-03 00:00:00
9 100001536 362098910 34806598 1 103924803 2011-04-03 00:00:00 2011-04-03 00:00:00
10 100001536 196823514 34806598 1 103924803 2011-04-03 00:00:00 2011-04-03 00:00:00


[MM-Writable@v031014.sqa.cm4 /tmp]
root@test 01:00:48>truncate tmp_qw_test;
Query OK, 10 rows affected (0.00 sec)

root@test 01:00:52>load data infile '/tmp/tmp_qw_test1.sql' into table tmp_qw_test(id,user_id,target_id,group_id,common_friend_count,common_friends,gmt_create,gmt_modified);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

root@test 01:07:20>select * from tmp_qw_test;+----+-----------+-----------+----------+---------------------+----------------+---------------------+---------------------+
| id | user_id   | target_id | group_id | common_friend_count | common_friends | gmt_create          | gmt_modified        |
+----+-----------+-----------+----------+---------------------+----------------+---------------------+---------------------+
|  1 | 100001536 |  11217864 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 2011-04-03 00:00:00 |
|  2 | 100001536 | 204886714 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 2011-04-03 00:00:00 |
|  3 | 100001536 |  48259232 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 2011-04-03 00:00:00 |
|  4 | 100001536 | 201869594 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 2011-04-03 00:00:00 |
|  5 | 100001536 |  76686624 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 2011-04-03 00:00:00 |
|  6 | 100001536 | 279932430 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 2011-04-03 00:00:00 |
|  7 | 100001536 |  54773280 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 2011-04-03 00:00:00 |
|  8 | 100001536 | 448342406 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 2011-04-03 00:00:00 |
|  9 | 100001536 | 362098910 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 2011-04-03 00:00:00 |
| 10 | 100001536 | 196823514 | 34806598 |                   1 | 103924803      | 2011-04-03 00:00:00 | 2011-04-03 00:00:00 |
+----+-----------+-----------+----------+---------------------+----------------+---------------------+---------------------+
10 rows in set (0.00 sec)