板凳-------Mysql cookbook学习 （十--15）

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}$' THENSTR_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}$' THENSTR_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}$' THENSTR_TO_DATE(CONCAT(event_date, '-01'), '%Y-%m-%d')-- 处理其他格式...ELSE event_dateEND
WHERE event_name IN ('Fred', 'Mort', 'Alice', 'Bob', 'Carol', 'Dave', 'Eve', 'Frank', 'Grace', 'Henry', 'Ivy', 'Jack', 'Karen', 'Leo', 'Mona');
注意事项
两位数年份处理规则：70-99 → 1970-199900-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: 2mysql>
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: 0mysql>
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: 0mysql>
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: 0mysql>
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: 0mysql> -- 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: 0mysql>
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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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.txtmysql> 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: 0mysql> 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)
SETval = '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)
SETc1 = 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)