-- =====================================================
-- MySQL load数据的小问题处理
-- =====================================================
对于字段类型date，load data infile时，如果不指定相应的FIELDS参数，错误的将日期用单引号引起来会导致插入错误的日期
mysql> show create table a;
+-------+------------------------------------------------------------------------------------------------------------
---------------------------------+
| Table | Create Table
|
+-------+------------------------------------------------------------------------------------------------------------
---------------------------------+
| a | CREATE TABLE `a` (
`id` int(11) NOT NULL DEFAULT '0',
`bd` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------
---------------------------------+
1 row in set (0.00 sec)
load的数据内容：
1 2009-10-1
2 '2008-12-2'
load结果：
mysql> load data infile 'a.txt' into table a;
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 1
mysql> select * from a;
+----+------------+
| id | bd |
+----+------------+
| 1 | 2009-10-01 |
| 2 | 0000-00-00 |
+----+------------+
2 rows in set (0.00 sec)
如果使用正确的FIELDS参数能得到正确的结果：
mysql> load data infile '/home/ddb/naturally/si-node/pmysql/a.txt' into table a FIELDS TERMINATED BY '\t' ENCLOSED BY
'\'' ESCAPED BY '\\';
Query OK, 2 rows affected (0.02 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from a;
+---注释-+------------+
| id | bd |
+----+------------+
| 1 | 2009-10-01 |
| 2 | 2008-12-02 |
+----+------------+
2 rows in set (0.00 sec)
【】mysql的load操作基本不做对于字段类型的检查以及转换，这也是为什么load数据的速度会是insert的几倍，也就要求了在使用
load操作的时候要注意，特别小心，防止出现意想不到的错误
