Mysql导入常见问题及个人收获

2023-04-22

遇到的问题及收获

列表：

时间类型datetime字段的值，当导入值为空时，默认值是什么，会报错吗
如果上述导入不会报错，那么再将此数据insert到相同表结构时，会报错吗
导入时，如果其中一行导入失败，会影响其他行的导入吗
批量insert时，如果其中一行insert失败，会影响其他行的导入吗
local_infile参数的用法
local_infile参数设置为ON后，为啥会出现自己自动变回OFF的情况
数值类型（比如decimal或integer），当导入值为空字符串时，值会转化为0。如何在导入后维持原值（null）
load data replace into和load data ignore into 有什么区别吗
load data语句如何实现先清空表再导入
load data什么场景下会发生死锁
导入有哪些常见的坑，常见的导入失败场景有哪些
load data会识别哪些转义字符
my.cnf权限问题：如何设置可以使得只有管理员用户可以查看密码
load data 若不指定编码集的情况下，默认使用什么编码进行导入？在导入时编码格式需要指定成什么是由表的编码而定还是以导入文件的编码而定？

时间类型datetime字段的值，当导入值为NULL时，默认值是什么，会报错吗

如果导入时没有指定空时的默认值时，会导入'0000-00-00 00:00:00'，并不会报错

如果上述导入不会报错，那么再将此数据insert到相同表结构时，会报错吗

会报错。主要是因为MYSQL的严格模式，在此模式下，'0000-00-00 00:00:00'被设为无效的日期值，不能被插入到datetime字段中，会报错：

Error Code: 1292. Incorrect datetime value: '0000-00-00 00:00:00' for column 'datetime_column' at row 1

如果要支持此类插入，需要禁用严格模式。

其实，在严格模式下的datetime字段，只有在导入或插入NULL时不会报错，空字符串和'0000-00-00 00:00:00'在导入和插入场景都会报错！只不过，导入NULL时会自动变成'0000-00-00 00:00:00'，插入NULL值还是维持NULL。

因此，如果我们的场景是需要先导入到临时表，再插入到正式表。使用Mysql的默认方式时，NULL会先转换为'0000-00-00 00:00:00'导入到临时表，当从临时表insert到正式表时，就会失败。一个解决方案是，在LOAD DATA语句中额外指定：当导入到datetime格式的值为NULL，则直接导入为NULL：

LOAD DATA INFILE 'filename'
INTO TABLE 'tablename'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE n LINES
(field1, @var1, field2, @var2, ...)
SET
field1 = COALESCE(NULLIF(@var1, ''), NULL),
field2 = COALESCE(NULLIF(@var2, ''), NULL),
...;

在上面的例子中，我们使用COALESCE函数对NULL值进行处理，将@var1的值与''进行比较，如果相等，则返回NULL，否则返回@var1的值。如果@var1为NULL，则NULLIF(@var1, '')返回NULL，而COALESCE(NULL, NULL)也会返回NULL。因此，最终将会将该字段设置为实际的NULL值。

导入时，如果其中一行导入失败，会影响其他行的导入吗

使用load data进行导入时，默认是以事务的方式运行的。即，其中一行导入失败的话，将中止导入过程，并回滚所有行。（这点存疑，需要验证） 如何使得某行导入遇到错误时忽略该行，继续导入其他行：

LOAD DATA ...
IGNORE INTO TABLE 'tablename'
...

IGNORE INTO TABLE表示导入时忽略重复键或数据类型错误错误（导入错误情况会记录在日志中，需要关注查看）（这点存疑，需要验证）

需要注意的是：如果数据类型不匹配，IGNORE INTO TABLE会使得忽略这类行的导入，而不会对数据进行类型转换后再导入该行。比如本来导入decimal字段时，值为空会被自动转换为0后导入，但是配置了这个IGNORE以后，就会直接不进行导入该行了。（这点存疑，需要验证）

批量insert时，如果其中一行insert失败，会影响其他行的导入吗

会被影响。如果使用的是普通的INSERT INTO语句，而非INSERT IGNORE INTO，那么比如第3行数据插入失败后，会导致之后的行（包括第4行和第5行等）都无法插入到表中，整个插入语句都会被中止。（这点存疑，需要验证）

可以使用INSERT IGNORE INTO在一定程度上防止影响其他行的插入，此时MySQL 会忽略一些错误，包括：

主键重复：如果插入的行的主键值已经存在于表中，MySQL 会忽略这个插入请求而不会报错。
唯一索引重复：如果插入的行的唯一索引值已经存在于表中，MySQL 会忽略这个插入请求而不会报错。
非空字段为空：如果插入的行中某些非空字段为空，MySQL 会将这些字段设置为默认值并插入一条记录，而不会报错。

注意：此时若遇到这类错误，可能就只是忽略导入该行，不会返回任何错误或警告信息（这是个坑）（这点存疑，需要验证）

需要注意的是，INSERT IGNORE INTO 仅会忽略部分错误，如语法错误、字段不存在等错误还是会导致插入失败。

local_infile参数的用法

local_infile是MySQL的一个系统变量，用于控制是否允许客户端通过LOAD DATA LOCAL INFILE语句从本地文件系统导入数据到MySQL服务器中。开启或关闭方法：

SET GLOBAL local_infile=1; -- 开启
SET GLOBAL local_infile=0; -- 关闭

local_infile参数设置为ON后，为啥会出现自己自动变回OFF的情况

有可能是因为该参数被设置为MySQL的默认值（比如重启后），或者是被其他配置文件覆盖了。Mysql重启后，会重置该参数为默认值。为了保持设置，可以在配置文件中进行设置，如在my.cnf中设置：

[mysql]
local-infile=1

注意：MYSQL重启后是否会重置这个参数！（这点存疑，需要验证）

数值类型（比如decimal或integer），当导入值为空字符串时，值会转化为0。如何在导入后维持原值（null）

和之前将的datetime格式导入空会自动转换为'0000-00-00 00:00:00'类似，使用LOAD DATA导入空时，Mysql会自动转换为该字段的默认值。对于数值类型，默认值就是0。如果不想要这种效果的话，可以使用set来特殊处理。例如：

LOAD DATA INFILE 'data.txt'
INTO TABLE mytable
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(@var1)
SET mycol = NULLIF(@var1, '');

即，若var1为空字符串时，则转换为NULL导入。

load data replace into和load data ignore into 有什么区别吗

两者都和“导入时存在相同主键的记录”处理相关。前者会替换掉原来的记录，后者会直接忽略。

但是，使用 REPLACE 或 IGNORE 可以在一定程度上忽略一些数据导入时的错误。（这点存疑，需要验证）

load data语句如何实现先清空表再导入

load data本身没有提供此类参数可以指定先清空再导入。只能在导入前先执行一次清空sql，如truncate table xxx

load data什么场景下会发生死锁

以下是charGPT的回答：在MySQL中，LOAD DATA语句会获取一个表级锁，如果同时有其他用户对该表进行写操作，则可能发生死锁。

以下是几种可能发生死锁的场景：

并发导入：当多个用户同时使用LOAD DATA语句导入数据到同一个表中时，由于LOAD DATA会获取表级锁，可能会导致死锁。例如，用户A和用户B同时导入数据到表T中。
并发插入：当多个用户同时使用INSERT INTO语句插入数据到同一个表中时，可能会与LOAD DATA语句产生竞争，导致死锁。例如，用户A使用LOAD DATA导入数据到表T中，同时用户B使用INSERT INTO语句向表T中插入数据。
导入大量数据：当导入的数据量很大时，LOAD DATA语句可能需要较长的时间来完成，而在此期间，其他用户可能会试图对该表进行写操作，从而导致死锁。

对于以上情况，可以通过以下几种方式来避免死锁：

使用LOAD DATA LOCAL语句：LOAD DATA LOCAL语句不会获取表级锁，而是在客户端上读取文件并将数据发送到服务器，因此可以避免死锁。
使用LOCK TABLES语句：在使用LOAD DATA语句之前，可以使用LOCK TABLES语句显式地锁定要导入数据的表，这样可以确保在LOAD DATA语句执行期间没有其他用户对表进行写操作。
尽量避免并发操作：可以通过限制并发操作的数量，或者将导入操作与其他写操作分开，来降低死锁的风险。

导入有哪些常见的坑，常见的导入失败场景有哪些

数据类型不匹配
字符编码
行终止符不一致
空值问题
文件权限问题（导入的数据文件需要有读取权限）
数据完整性（如有外键约束时）等等

load data会识别哪些转义字符

https://dev.mysql.com/doc/refman/8.0/en/string-literals.html

以下这些后面再整理

转义符

escaprd by ''

datetime导入时为空

https://www.cnblogs.com/nayek/p/12915570.html

NO_ZERO_IN_DATE 和 NO_ZERO_DATE 都是 MySQL 的 SQL 模式，它们的作用是限制插入的日期或时间值的格式。

NO_ZERO_IN_DATE 的作用是禁止插入日期格式中出现 00，例如：'2023-00-20'，此时 MySQL 会抛出 ERROR 1292 (22007): Incorrect date value: '2023-00-20' for column 'my_date' at row 1 错误。这个模式不会影响时间格式的值。

NO_ZERO_DATE 的作用是禁止插入时间格式中出现全为 0 的日期，例如：'0000-00-00 00:00:00'，此时 MySQL 会抛出 ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'my_datetime' at row 1 错误。

区别在于 NO_ZERO_IN_DATE 只限制日期格式，NO_ZERO_DATE 则限制时间格式，包括日期和时间。如果这两个模式都开启了，则插入的日期或时间值必须是合法的，不能包含 00 或全为 0 的日期。这些限制可以帮助保证数据库中的数据的准确性和一致性。

https://dev.mysql.com/doc/refman/8.0/en/load-data.html LOAD DATA interprets an empty field value differently from a missing field:

For string types, the column is set to the empty string.
For numeric types, the column is set to 0.
For date and time types, the column is set to the appropriate “zero” value for the type. See Section 11.2, “Date and Time Data Types”. These are the same values that result if you assign an empty string explicitly to a string, numeric, or date or time type explicitly in an INSERT or UPDATE statement.

Server SQL Modes https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html