10.31 编写时间处理工具
sql
-- 创建测试数据
-- 插入所有原始数据，使用STR_TO_DATE函数处理不同格式的日期
INSERT INTO date_test (event_name, event_date) VALUES
('Fred', STR_TO_DATE('04-13-70', '%m-%d-%y')),
('Mort', STR_TO_DATE('09-3-69', '%m-%d-%y')),
('Alice', STR_TO_DATE('2023-05', '%Y-%m')),
('Bob', STR_TO_DATE('11/15/22', '%m/%d/%y')),
('Carol', '2023-02-28'), -- 已经是标准格式，无需转换
('Dave', STR_TO_DATE('7-8-85', '%c-%e-%y')),
('Eve', STR_TO_DATE('12-1-00', '%m-%d-%y')),
('Frank', STR_TO_DATE('2023', '%Y')),
('Grace', STR_TO_DATE('05/2023', '%m/%Y')),
('Henry', STR_TO_DATE('Jan-15-2023', '%b-%d-%Y')),
('Ivy', STR_TO_DATE('15-Mar-2023', '%d-%b-%Y')),
('Jack', STR_TO_DATE('20230515', '%Y%m%d')),
('Karen', STR_TO_DATE('23.05.15', '%y.%m.%d')),
('Leo', STR_TO_DATE('5/3', '%m/%d')),
('Mona', NULL);
-- 验证数据
SELECT * FROM date_test WHERE event_name IN ('Fred', 'Mort', 'Alice', 'Bob', 'Carol', 'Dave', 'Eve', 'Frank', 'Grace', 'Henry', 'Ivy', 'Jack', 'Karen', 'Leo', 'Mona');
数据处理脚本
如果需要将这些不同格式的日期统一转换为标准格式，可以使用以下SQL：
sql
UPDATE date_test
SET event_date =
CASE
-- 处理 MM-DD-YY 格式 (70-99→1970-1999, 00-69→2000-2069)
WHEN event_date REGEXP '^[0-9]{1,2}-[0-9]{1,2}-[0-9]{2}$' THEN
STR_TO_DATE(
CONCAT(
IF(SUBSTRING_INDEX(SUBSTRING_INDEX(event_date, '-', -1), '-', 2) >= 70, '19', '20'),
SUBSTRING_INDEX(SUBSTRING_INDEX(event_date, '-', -1), '-', 2),
'-',
LPAD(SUBSTRING_INDEX(event_date, '-', 1), 2, '0'),
'-',
LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(event_date, '-', 2), '-', -1), 2, '0')
),
'%Y-%m-%d'
)
-- 处理 MM/DD/YY 格式
WHEN event_date REGEXP '^[0-9]{1,2}/[0-9]{1,2}/[0-9]{2}$' THEN
STR_TO_DATE(
CONCAT(
IF(SUBSTRING_INDEX(SUBSTRING_INDEX(event_date, '/', -1), '/', 2) >= 70, '19', '20'),
SUBSTRING_INDEX(SUBSTRING_INDEX(event_date, '/', -1), '/', 2),
'-',
LPAD(SUBSTRING_INDEX(event_date, '/', 1), 2, '0'),
'-',
LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(event_date, '/', 2), '/', -1), 2, '0')
),
'%Y-%m-%d'
)
-- 处理 YYYY-MM 格式
WHEN event_date REGEXP '^[0-9]{4}-[0-9]{1,2}$' THEN
STR_TO_DATE(CONCAT(event_date, '-01'), '%Y-%m-%d')
-- 处理其他格式...
ELSE event_date
END
WHERE event_name IN ('Fred', 'Mort', 'Alice', 'Bob', 'Carol', 'Dave', 'Eve', 'Frank', 'Grace', 'Henry', 'Ivy', 'Jack', 'Karen', 'Leo', 'Mona');
注意事项
两位数年份处理规则：
70-99 → 1970-1999
00-69 → 2000-2069
对于无法自动转换的复杂格式(如'Jan-15-2023')，建议：
在应用层预处理
或使用存储过程专门处理
空值(NULL)会保持不变
转换后建议验证数据：
sql
SELECT event_name, event_date
FROM date_test
WHERE event_date IS NOT NULL
ORDER BY event_date;
10.32 使用不完整的日期
mysql> -- 创建测试表
mysql> CREATE TABLE IF NOT EXISTS date_test (
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> event_name VARCHAR(50) NOT NULL,
-> event_date DATE, -- 仅日期
-> event_datetime DATETIME, -- 日期+时间
-> created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
-> );
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> -- 查看表结构
mysql> DESCRIBE date_test;
+----------------+-------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+-------------------+-------------------+
| id | int | NO | PRI | NULL | auto_increment |
| event_name | varchar(50) | NO | | NULL | |
| event_date | date | YES | | NULL | |
| event_datetime | datetime | YES | | NULL | |
| created_at | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+----------------+-------------+------+-----+-------------------+-------------------+
5 rows in set (0.01 sec)
mysql> -- 1. 临时禁用严格模式
mysql> SET @@sql_mode = '';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> -- 2. 重新尝试插入数据
mysql> INSERT INTO date_test (event_name, event_date, event_datetime)
-> VALUES
-> ('仅年月', '2023-05', NULL),
-> ('仅日期无时间', '2023-05-20', NULL),
-> ('错误分隔符', '2023/05/20', NULL),
-> ('两位数年份', '23-05-20', NULL),
-> ('空日期', NULL, NULL);
Query OK, 5 rows affected, 2 warnings (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 2
mysql>
mysql> -- 3. 恢复严格模式（可选）
mysql> SET @@sql_mode = 'STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> -- 1. 将DATE类型改为VARCHAR以存储各种格式
mysql> ALTER TABLE date_test MODIFY event_date VARCHAR(10);
Query OK, 6 rows affected (0.12 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>
mysql> -- 2. 插入数据
mysql> INSERT INTO date_test (event_name, event_date, event_datetime)
-> VALUES
-> ('仅年月', '2023-05', NULL),
-> ('仅日期无时间', '2023-05-20', NULL),
-> ('错误分隔符', '2023/05/20', NULL),
-> ('两位数年份', '23-05-20', NULL),
-> ('空日期', NULL, NULL);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql>
mysql> -- 3. 添加一个真正的DATE列用于存储规范化的日期
mysql> ALTER TABLE date_test ADD COLUMN normalized_date DATE;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> -- 4. 转换数据
mysql> UPDATE date_test SET normalized_date =
-> CASE
-> WHEN event_date LIKE '____-__-__' THEN STR_TO_DATE(event_date, '%Y-%m-%d')
-> WHEN event_date LIKE '____-__' THEN STR_TO_DATE(CONCAT(event_date, '-01'), '%Y-%m-%d')
-> WHEN event_date LIKE '__-__-__' THEN STR_TO_DATE(
-> CONCAT(IF(SUBSTRING(event_date,1,2)>'70','19','20'),
-> SUBSTRING(event_date,1,2), '-', SUBSTRING(event_date,4,2), '-', SUBSTRING(event_date,7,2)),
-> '%Y-%m-%d')
-> WHEN event_date LIKE '%/%' THEN STR_TO_DATE(event_date, '%Y/%m/%d')
-> ELSE NULL
-> END;
Query OK, 9 rows affected (0.01 sec)
Rows matched: 11 Changed: 9 Warnings: 0
mysql> -- 1. 创建临时表
mysql> CREATE TEMPORARY TABLE temp_dates (
-> event_name VARCHAR(50),
-> event_date VARCHAR(10),
-> event_datetime VARCHAR(20)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> -- 2. 插入各种格式的数据
mysql> INSERT INTO temp_dates VALUES
-> ('仅年月', '2023-05', NULL),
-> ('仅日期无时间', '2023-05-20', NULL),
-> ('错误分隔符', '2023/05/20', NULL),
-> ('两位数年份', '23-05-20', NULL),
-> ('空日期', NULL, NULL);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql>
mysql> -- 3. 转换后插入正式表
mysql> INSERT INTO date_test (event_name, event_date, event_datetime)
-> SELECT
-> event_name,
-> CASE
-> WHEN event_date LIKE '____-__-__' THEN STR_TO_DATE(event_date, '%Y-%m-%d')
-> WHEN event_date LIKE '____-__' THEN STR_TO_DATE(CONCAT(event_date, '-01'), '%Y-%m-%d')
-> WHEN event_date LIKE '__-__-__' THEN STR_TO_DATE(
-> CONCAT(IF(LEFT(event_date,2)>'70','19','20'),
-> LEFT(event_date,2), '-', MID(event_date,4,2), '-', RIGHT(event_date,2)),
-> '%Y-%m-%d')
-> WHEN event_date LIKE '%/%' THEN STR_TO_DATE(event_date, '%Y/%m/%d')
-> ELSE NULL
-> END,
-> event_datetime
-> FROM temp_dates;
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM date_test;
+----+--------------+------------+---------------------+---------------------+-----------------+
| id | event_name | event_date | event_datetime | created_at | normalized_date |
+----+--------------+------------+---------------------+---------------------+-----------------+
| 1 | 正确日期 | 2023-05-15 | 2023-05-15 14:30:00 | 2025-06-29 16:20:14 | 2023-05-15 |
| 2 | 仅年月 | 0000-00-00 | NULL | 2025-06-29 16:21:27 | 0000-00-00 |
| 3 | 仅日期无时间 | 2023-05-20 | NULL | 2025-06-29 16:21:27 | 2023-05-20 |
| 4 | 错误分隔符 | 2023-05-20 | NULL | 2025-06-29 16:21:27 | 2023-05-20 |
| 5 | 两位数年份 | 2023-05-20 | NULL | 2025-06-29 16:21:27 | 2023-05-20 |
| 6 | 空日期 | NULL | NULL | 2025-06-29 16:21:27 | NULL |
| 7 | 仅年月 | 2023-05 | NULL | 2025-06-29 16:21:46 | 2023-05-01 |
| 8 | 仅日期无时间 | 2023-05-20 | NULL | 2025-06-29 16:21:46 | 2023-05-20 |
| 9 | 错误分隔符 | 2023/05/20 | NULL | 2025-06-29 16:21:46 | 2023-05-20 |
| 10 | 两位数年份 | 23-05-20 | NULL | 2025-06-29 16:21:46 | 2023-05-20 |
| 11 | 空日期 | NULL | NULL | 2025-06-29 16:21:46 | NULL |
| 12 | 仅年月 | 2023-05-01 | NULL | 2025-06-29 16:22:07 | NULL |
| 13 | 仅日期无时间 | 2023-05-20 | NULL | 2025-06-29 16:22:07 | NULL |
| 14 | 错误分隔符 | 2023-05-20 | NULL | 2025-06-29 16:22:07 | NULL |
| 15 | 两位数年份 | 2023-05-20 | NULL | 2025-06-29 16:22:07 | NULL |
| 16 | 空日期 | NULL | NULL | 2025-06-29 16:22:07 | NULL |
+----+--------------+------------+---------------------+---------------------+-----------------+
16 rows in set (0.00 sec)
mysql> -- 示例触发器
mysql> DELIMITER //
mysql> CREATE TRIGGER format_date_before_insert
-> BEFORE INSERT ON date_test
-> FOR EACH ROW
-> BEGIN
-> IF NEW.event_date IS NOT NULL THEN
-> -- 转换各种格式为标准日期
-> SET NEW.event_date =
-> CASE
-> WHEN NEW.event_date LIKE '____-__-__' THEN STR_TO_DATE(NEW.event_date, '%Y-%m-%d')
-> WHEN NEW.event_date LIKE '____-__' THEN STR_TO_DATE(CONCAT(NEW.event_date, '-01'), '%Y-%m-%d')
-> WHEN NEW.event_date LIKE '__-__-__' THEN STR_TO_DATE(
-> CONCAT(IF(LEFT(NEW.event_date,2)>'70','19','20'),
-> LEFT(NEW.event_date,2), '-', MID(NEW.event_date,4,2), '-', RIGHT(NEW.event_date,2)),
-> '%Y-%m-%d')
-> WHEN NEW.event_date LIKE '%/%' THEN STR_TO_DATE(NEW.event_date, '%Y/%m/%d')
-> ELSE NULL
-> END;
-> END IF;
-> END//
Query OK, 0 rows affected (0.02 sec)
mysql> DELIMITER ;
mysql> -- 测试触发器拒绝不完整日期
mysql> INSERT INTO date_test (event_name, event_date)
-> VALUES ('测试不完整日期', '2023-05'); -- 这将触发错误
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> -- 测试触发器自动补全时间
mysql> INSERT INTO date_test (event_name, event_date)
-> VALUES ('测试自动补全', '2023-06-01'); -- 将自动添加时间
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> -- 查看结果
mysql> SELECT * FROM date_test WHERE event_name LIKE '测试%';
+----+----------------+------------+----------------+---------------------+-----------------+
| id | event_name | event_date | event_datetime | created_at | normalized_date |
+----+----------------+------------+----------------+---------------------+-----------------+
| 19 | 测试不完整日期 | 2023-05-01 | NULL | 2025-06-29 16:24:32 | NULL |
| 20 | 测试自动补全 | 2023-06-01 | NULL | 2025-06-29 16:24:32 | NULL |
+----+----------------+------------+----------------+---------------------+-----------------+
2 rows in set (0.01 sec)
mysql> -- 查看修复后的所有数据
mysql> SELECT
-> id,
-> event_name,
-> event_date,
-> event_datetime,
-> DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') AS created_at
-> FROM date_test
-> ORDER BY id;
+----+----------------+------------+---------------------+---------------------+
| id | event_name | event_date | event_datetime | created_at |
+----+----------------+------------+---------------------+---------------------+
| 1 | 正确日期 | 2023-05-15 | 2023-05-15 14:30:00 | 2025-06-29 16:20:14 |
| 2 | 仅年月 | 0000-00-00 | NULL | 2025-06-29 16:21:27 |
| 3 | 仅日期无时间 | 2023-05-20 | NULL | 2025-06-29 16:21:27 |
| 4 | 错误分隔符 | 2023-05-20 | NULL | 2025-06-29 16:21:27 |
| 5 | 两位数年份 | 2023-05-20 | NULL | 2025-06-29 16:21:27 |
| 6 | 空日期 | NULL | NULL | 2025-06-29 16:21:27 |
| 7 | 仅年月 | 2023-05 | NULL | 2025-06-29 16:21:46 |
| 8 | 仅日期无时间 | 2023-05-20 | NULL | 2025-06-29 16:21:46 |
| 9 | 错误分隔符 | 2023/05/20 | NULL | 2025-06-29 16:21:46 |
| 10 | 两位数年份 | 23-05-20 | NULL | 2025-06-29 16:21:46 |
| 11 | 空日期 | NULL | NULL | 2025-06-29 16:21:46 |
| 12 | 仅年月 | 2023-05-01 | NULL | 2025-06-29 16:22:07 |
| 13 | 仅日期无时间 | 2023-05-20 | NULL | 2025-06-29 16:22:07 |
| 14 | 错误分隔符 | 2023-05-20 | NULL | 2025-06-29 16:22:07 |
| 15 | 两位数年份 | 2023-05-20 | NULL | 2025-06-29 16:22:07 |
| 16 | 空日期 | NULL | NULL | 2025-06-29 16:22:07 |
| 19 | 测试不完整日期 | 2023-05-01 | NULL | 2025-06-29 16:24:32 |
| 20 | 测试自动补全 | 2023-06-01 | NULL | 2025-06-29 16:24:32 |
+----+----------------+------------+---------------------+---------------------+
18 rows in set (0.00 sec)
10.33 导入非iso格式日期值
mysql> describe t;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1 | varchar(64) | YES | | NULL | |
| c2 | int | YES | | NULL | |
| c3 | date | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> LOAD DATA LOCAL INFILE "D:\\sql\\MySQL_cookbook\\mysqlcookbook-master\\recipes\\transfer\\newdata.txt"
-> INTO TABLE t
-> FIELDS TERMINATED BY '\t'
-> LINES TERMINATED BY '\n'
-> (@name, @date, @value)
-> SET
-> c1 = @name,
-> c2 = @value,
-> c3 = STR_TO_DATE(@date, '%m/%d/%y');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
mysql> ALTER TABLE t
-> CHANGE COLUMN c1 name VARCHAR(64),
-> CHANGE COLUMN c2 value INT,
-> CHANGE COLUMN c3 date DATE;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> truncate t;
Query OK, 0 rows affected (0.08 sec)
mysql> LOAD DATA LOCAL INFILE "D:\\sql\\MySQL_cookbook\\mysqlcookbook-master\\recipes\\transfer\\newdata.txt"
-> INTO TABLE t
-> FIELDS TERMINATED BY '\t'
-> LINES TERMINATED BY '\n'
-> (name, @date, value)
-> SET date = STR_TO_DATE(@date, '%m/%d/%y');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from t;
+-------+-------+------------+
| name | value | date |
+-------+-------+------------+
| name1 | 38 | 1999-01-01 |
| name2 | 40 | 2000-12-31 |
| name3 | 42 | 2013-02-28 |
| name4 | 44 | 2018-01-02 |
+-------+-------+------------+
4 rows in set (0.00 sec)
10.34 使用非iso格式导出日期值
常用日期格式说明符
说明符 含义 示例
%Y 四位年份 2022
%y 两位年份 22
%m 月份 (01-12) 01
%d 日 (01-31) 14
%H 小时 (00-23) 14
%h 小时 (01-12) 02
%i 分钟 (00-59) 05
%s 秒 (00-59) 30
%p AM 或 PM PM
%W 星期名称 Friday
%a 缩写的星期名称 Fri
%M 月份名称 January
%b 缩写的月份名称 Jan
mysql> select * from datetbl;
+---+------+------------+---------------------+---------------------+
| i | c | d | dt | ts |
+---+------+------------+---------------------+---------------------+
| 3 | abc | 2022-01-14 | 2022-01-14 00:57:01 | 2022-01-14 05:57:01 |
| 4 | xyz | 2022-02-14 | 2022-02-14 00:57:01 | 2022-02-14 05:57:01 |
+---+------+------------+---------------------+---------------------+
2 rows in set (0.00 sec)
mysql> SELECT i, c,
-> DATE_FORMAT(d, '%m-%d-%y') AS d,
-> DATE_FORMAT(dt, '%m-%d-%y %H:%i:%s') AS dt,
-> DATE_FORMAT(ts, '%m-%d-%y %H:%i:%s') AS ts
-> FROM datetbl;
+---+------+----------+-------------------+-------------------+
| i | c | d | dt | ts |
+---+------+----------+-------------------+-------------------+
| 3 | abc | 01-14-22 | 01-14-22 00:57:01 | 01-14-22 05:57:01 |
| 4 | xyz | 02-14-22 | 02-14-22 00:57:01 | 02-14-22 05:57:01 |
+---+------+----------+-------------------+-------------------+
2 rows in set (0.00 sec)
mysql> SELECT i, c,
-> DATE_FORMAT(d, '%m-%d-%Y') AS d,
-> DATE_FORMAT(dt, '%m-%d-%Y %H:%i:%s') AS dt,
-> DATE_FORMAT(ts, '%m-%d-%Y %H:%i:%s') AS ts
-> FROM datetbl;
+---+------+------------+---------------------+---------------------+
| i | c | d | dt | ts |
+---+------+------------+---------------------+---------------------+
| 3 | abc | 01-14-2022 | 01-14-2022 00:57:01 | 01-14-2022 05:57:01 |
| 4 | xyz | 02-14-2022 | 02-14-2022 00:57:01 | 02-14-2022 05:57:01 |
+---+------+------------+---------------------+---------------------+
2 rows in set (0.00 sec)
perl cvt_date.pl "D:\sql\MySQL_cookbook\mysqlcookbook-master\recipes\transfer\newdata.txt" > newdata_mysql.txt
这个命令转换的误差太大了。
# 将 MM-DD-YY 转换为 YYYY-MM-DD
sed -E 's#([0-9]{2})-([0-9]{2})-([0-9]{2})#20\3-\1-\2#g' newdata.txt > newdata_mysql.txt
newdata.txt 文件使用的是斜杠 / 作为分隔符（如 01/01/99），但 sed 命令中却尝试匹配连字符 -（([0-9]{2})-([0-9]{2})-([0-9]{2})）。
将原先日期顺序改变 YY 格式下，00-69 → 2000-2069，70-99 → 1970-1999
C:\Users\lenovo>sed -E 's#([0-9]{2})/([0-9]{2})/([0-9]{2})#20\3-\1-\2#g' "D:\sql\MySQL_cookbook\mysqlcookbook-master\recipes\transfer\newdata.txt" > newdata_mysql.txt
修正转换逻辑
修改转换脚本，正确处理99→1999的转换：
bash
# 转换脚本（Linux/Mac）
sed -E 's#([0-9]{2})/([0-9]{2})/([0-9]{2})#\3 \1 \2#g' newdata.txt |
awk '{
year = $3;
if (year >= 70) year = "19" year;
else year = "20" year;
print $1 "\t" year "-" $2 "-" $4 "\t" $5
}' > newdata_mysql.txt
mysql> TRUNCATE t;
Query OK, 0 rows affected (0.09 sec)
mysql> LOAD DATA LOCAL INFILE 'D:/sql/MySQL_cookbook/mysqlcookbook-master/recipes/transfer/newdata.txt'
-> INTO TABLE t
-> FIELDS TERMINATED BY '\t'
-> LINES TERMINATED BY '\n'
-> (name, @date, value)
-> SET date = STR_TO_DATE(@date, '%m/%d/%y'); -- 明确指定格式为MM/DD/YY
Query OK, 4 rows affected (0.01 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from t;
+-------+-------+------------+
| name | value | date |
+-------+-------+------------+
| name1 | 38 | 1999-01-01 |
| name2 | 40 | 2000-12-31 |
| name3 | 42 | 2013-02-28 |
| name4 | 44 | 2018-01-02 |
+-------+-------+------------+
4 rows in set (0.00 sec)
10.35 导入和导出null值
mysql --local-infile=1 -u cbuser -p
*******
SHOW TABLES LIKE 't';
DESCRIBE t;
方法 1：为 val 列设置默认值
sql
复制
下载
LOAD DATA LOCAL INFILE "D:\\sql\\MySQL_cookbook\\mysqlcookbook-master\\recipes\\transfer\\has_nulls.txt"
INTO TABLE t
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
(@c1, @c2, @c3)
SET
val = 'default_value', -- 设置默认值或使用其他逻辑
c1 = NULLIF(@c1, 'unknown'),
c2 = NULLIF(@c2, -1),
c3 = NULLIF(@c3, '');
方法 2：修改表结构（删除 val 列）
sql
复制
下载
ALTER TABLE t DROP COLUMN val;
然后重新导入：
sql
复制
下载
LOAD DATA LOCAL INFILE "D:\\sql\\MySQL_cookbook\\mysqlcookbook-master\\recipes\\transfer\\has_nulls.txt"
INTO TABLE t
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
(@c1, @c2, @c3)
SET
c1 = NULLIF(@c1, 'unknown'),
c2 = NULLIF(@c2, -1),
c3 = NULLIF(@c3, '');
mysql> describe t;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1 | varchar(64) | YES | | NULL | |
| c2 | int | YES | | NULL | |
| c3 | date | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> SELECT c1, c2, c3 FROM t;
+------+------+------------+
| c1 | c2 | c3 |
+------+------+------------+
| str1 | 13 | 1997-10-14 |
| str2 | NULL | 2009-05-07 |
| NULL | 15 | NULL |
| NULL | NULL | 1973-07-15 |
+------+------+------------+
4 rows in set (0.00 sec)