linux下mysql如何导入换行的数据?

shengang34

mysql> \! cat /tmp/s1.txt
SQLSNAP;2009-04-28
09:36:20$
SQLSNAP;2009-04-28
09:36:20$
SQLSNAP;2009-04-28
09:36:20$
mysql>
mysql> truncate table t;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> load data infile '/tmp/s1.txt' into table t fields terminated by ';' enclosed by '' lines terminated by '$\n';
Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from t;
+---------+---------------------+
| t1      | t2                  |
+---------+---------------------+
| SQLSNAP | 2009-04-28 09:36:20 |
| SQLSNAP | 2009-04-28 09:36:20 |
| SQLSNAP | 2009-04-28 09:36:20 |
+---------+---------------------+
3 rows in set (0.00 sec)

mysql> desc t;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| t1    | varchar(255) | YES  |     | NULL    |       |
| t2    | datetime     | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)


晕，你都没测试吧？datetime能导入，字段去掉空行了。

xzh2000

原帖由 shengang34 于 2009-8-27 11:31 发表
mysql> \! cat /tmp/s1.txt
SQLSNAP;2009-04-28
09:36:20$
SQLSNAP;2009-04-28
09:36:20$
SQLSNAP;2009-04-28
09:36:20$
mysql>
mysql> truncate table t;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> load data infile '/tmp/s1.txt' into table t fields terminated by ';' enclosed by '' lines terminated by '$\n';
Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from t;
+---------+---------------------+
| t1      | t2                  |
+---------+---------------------+
| SQLSNAP | 2009-04-28 09:36:20 |
| SQLSNAP | 2009-04-28 09:36:20 |
| SQLSNAP | 2009-04-28 09:36:20 |
+---------+---------------------+
3 rows in set (0.00 sec)

mysql> desc t;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| t1    | varchar(255) | YES  |     | NULL    |       |
| t2    | datetime     | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)


晕，你都没测试吧？datetime能导入，字段去掉空行了。

mysql> desc tmp_sqlplan;
+-----------------+--------------+------+-----+---------------------+-------+
| Field           | Type         | Null | Key | Default             | Extra |
+-----------------+--------------+------+-----+---------------------+-------+
| service         | varchar(12)  | YES  |     | NULL                |       |
| sql_id          | varchar(13)  | YES  |     | NULL                |       |
| plan_hash_value | bigint(20)   | YES  |     | NULL                |       |
| plan_id         | int(11)      | YES  |     | NULL                |       |
| plan_line       | varchar(255) | YES  |     | NULL                |       |
| timestamp       | datetime     | YES  |     | NULL                |       |
| plan_type       | varchar(20)  | YES  |     | NULL                |       |
| gmt_create      | timestamp    | NO   |     | 0000-00-00 00:00:00 |       |
+-----------------+--------------+------+-----+---------------------+-------+
8 rows in set (0.01 sec)

mysql> load data infile '/tmp/sqlplan.log' into table tmp_sqlplan fields terminated by ";"  
    ->       enclosed by '' LINES STARTING BY 'A;' TERMINATED BY '$\n'
    ->   (service, sql_id, plan_hash_value, plan_id, plan_line, plan_type, timestamp);
Query OK, 3 rows affected, 3 warnings (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 3

mysql> select sql_id, plan_type, timestamp from tmp_sqlplan;
+---------------+-----------+---------------------+
| sql_id        | plan_type | timestamp           |
+---------------+-----------+---------------------+
| 9185hm8hq089c | SQLTEST   | 0000-00-00 00:00:00 |
| 9185hm8hq089c | SQLTEST   | 0000-00-00 00:00:00 |
| 9185hm8hq089c | SQLTEST   | 0000-00-00 00:00:00 |
+---------------+-----------+---------------------+
3 rows in set (0.00 sec)

mysql> alter table tmp_sqlplan modify timestamp varchar(20);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select sql_id, plan_type, timestamp from tmp_sqlplan;
+---------------+-----------+---------------------+
| sql_id        | plan_type | timestamp           |
+---------------+-----------+---------------------+
| 9185hm8hq089c | SQLTEST   | 0000-00-00 00:00:00 |
| 9185hm8hq089c | SQLTEST   | 0000-00-00 00:00:00 |
| 9185hm8hq089c | SQLTEST   | 0000-00-00 00:00:00 |
+---------------+-----------+---------------------+
3 rows in set (0.00 sec)

mysql> load data infile '/tmp/sqlplan.log' into table tmp_sqlplan fields terminated by ";"  
    ->       enclosed by '' LINES STARTING BY 'A;' TERMINATED BY '$\n'
    ->   (service, sql_id, plan_hash_value, plan_id, plan_line, plan_type, timestamp);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select sql_id, plan_type, timestamp from tmp_sqlplan;
+---------------+-----------+----------------------+
| sql_id        | plan_type | timestamp            |
+---------------+-----------+----------------------+
| 9185hm8hq089c | SQLTEST   | 0000-00-00 00:00:00  |
| 9185hm8hq089c | SQLTEST   | 0000-00-00 00:00:00  |
| 9185hm8hq089c | SQLTEST   | 0000-00-00 00:00:00  |
| 9185hm8hq089c | SQLTEST   | 2009-04-28 0
9:36:20 |
| 9185hm8hq089c | SQLTEST   | 2009-04-2
8 09:36:20 |
| 9185hm8hq089c | SQLTEST   | 2009-04-28 09:3
6:20 |
+---------------+-----------+----------------------+
6 rows in set (0.00 sec)

你看看，偶在linux下的测试结果。。。

more sqlplan.log
A;test1;9185hm8hq089c;2413334117;11;|                         |     1 |    8 |      1 |;SQLTEST;2009-04-28 0
9:36:20$
A;test1;9185hm8hq089c;2413334117;10;|                      |                     |       |      |        |;SQLTEST;2009-04-2
8 09:36:20$
A;test1;9185hm8hq089c;2413334117;9;|                   |                     |     3 |   24 |      5 |;SQLTEST;2009-04-28 09:3
6:20$

shengang34

2009-04-28 0
9:36:20$
关键在这个地方没有规律。如果是
2009-04-28
09:36:20$
日期和时间能完全分2行，可以使用上面的方法，如果是乱的话也有方法，如下：
mysql> \! cat /tmp/s1.txt
SQLSNAP;2009-04-2
8 09:36:20$
SQLSNAP;2009-04-28 0
9:36:20$
SQLSNAP;2009-04-28 09
:36:20$
mysql>
mysql>
mysql> load data infile '/tmp/s1.txt' into table t fields terminated by ';' enclosed by '' ESCAPED BY '\n' lines terminated by '$\n';
Query OK, 3 rows affected (0.02 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from t;
+---------+---------------------+
| t1      | t2                  |
+---------+---------------------+
| SQLSNAP | 2009-04-28 09:36:20 |
| SQLSNAP | 2009-04-28 09:36:20 |
| SQLSNAP | 2009-04-28 09:36:20 |
+---------+---------------------+
3 rows in set (0.00 sec)

注意ESCAPED BY '\n'，就是把空行转译了。这样
2009-04-2
8 09:36:20$
就会看做2009-04-28 09:36:20$导入，但如果正好日期和时间完全分开，例如
2009-04-28
09:36:20$  这样就会转译成2009-04-2809:36:20$，就会报错。

还是把spool的结果能够规范一点是最好。

xzh2000

原帖由 shengang34 于 2009-8-27 13:55 发表
2009-04-28 0
9:36:20$
关键在这个地方没有规律。如果是
2009-04-28
09:36:20$
日期和时间能完全分2行，可以使用上面的方法，如果是乱的话也有方法，如下：
mysql> \! cat /tmp/s1.txt
SQLSNAP;2009-04-2
8 09:36:20$
SQLSNAP;2009-04-28 0
9:36:20$
SQLSNAP;2009-04-28 09
:36:20$
mysql>
mysql>
mysql> load data infile '/tmp/s1.txt' into table t fields terminated by ';' enclosed by '' ESCAPED BY '\n' lines terminated by '$\n';
Query OK, 3 rows affected (0.02 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from t;
+---------+---------------------+
| t1      | t2                  |
+---------+---------------------+
| SQLSNAP | 2009-04-28 09:36:20 |
| SQLSNAP | 2009-04-28 09:36:20 |
| SQLSNAP | 2009-04-28 09:36:20 |
+---------+---------------------+
3 rows in set (0.00 sec)

注意ESCAPED BY '\n'，就是把空行转译了。这样
2009-04-2
8 09:36:20$
就会看做2009-04-28 09:36:20$导入，但如果正好日期和时间完全分开，例如
2009-04-28
09:36:20$  这样就会转译成2009-04-2809:36:20$，就会报错。

还是把spool的结果能够规范一点是最好。

谢谢，与偶要的结果差不多了，现在是用varchar类型，这样数据导入后，再处理一下数据即可。