禁用 MySQL 严格模式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11040207/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Disabling MySQL Strict Mode
提问by Orlan
I am scanning 5000 csv files into a database. Regrettably the files have '', for 0. Thus when ever I run my script it crashes. I heard that this error is possible to avoid by simply disabling strict mode. So I attempted to disable strict mode to allow me to read in an empty string as a 0 for my numeric fields. However the error persisted.
我正在将 5000 个 csv 文件扫描到数据库中。遗憾的是，这些文件的 '', 为 0。因此，每当我运行我的脚本时，它就会崩溃。我听说只要禁用严格模式就可以避免这个错误。因此，我尝试禁用严格模式以允许我将空字符串读取为数字字段的 0。但是错误仍然存在。
So does disabling strict mode allow '' to be read into a int field? (the '' is two qoutes i.e. empty string)
那么禁用严格模式是否允许 '' 被读入 int 字段？（'' 是两个 qoutes，即空字符串）
If so why did setting
如果是这样，为什么设置
sql_mode=''
sql_mode=''
in the my.ini config file not fix the problem.
在 my.ini 配置文件中没有解决问题。
Thank you!
谢谢！
回答by jkrcma
I guess you import the CSV file using LOAD DATA INFILEcommand. Before you execute this command, type:
我猜您是使用LOAD DATA INFILE命令导入 CSV 文件的。在执行此命令之前，请键入：
SET sql_mode = '';
More information about various SQL modes can be found in the documentation.
可以在文档中找到有关各种 SQL 模式的更多信息。
回答by Apkahym
I need do it this, and work correctly:
我需要这样做，并正常工作：
- To disable strict SQL mode, SSH in to your server as root
- Create this file: /etc/mysql/conf.d/disable_strict_mode.cnf
Open the file and enter these two lines:
[mysqld] sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Restart MySQL with this command: sudo service mysql restart
- 要禁用严格的 SQL 模式，请以 root 身份通过 SSH 连接到您的服务器
- 创建这个文件：/etc/mysql/conf.d/disable_strict_mode.cnf
打开文件并输入以下两行：
[mysqld] sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
使用此命令重新启动 MySQL：sudo service mysql restart
回答by Kibbee
You can modify you LOAD DATA INFILEStatement to correct the values. Something along these lines should work.
您可以修改LOAD DATA INFILE语句以更正这些值。沿着这些路线的东西应该工作。
LOAD DATA INFILE 'filepath.csv'
INTO TABLE MyTable(Column1,@Col2,@Col3)
SET Column2=CASE WHEN @Col2 = '' THEN 0 ELSE @Col2 END
,Column3=CASE WHEN @Col3 = '' THEN 0 ELSE @Col3 END;
This query imports the value as is into column1, and the fixes the values for columns 2 and 3. Using this, you don't have to disable strict mode, and you are actually in control of what data goes into your database, are are able to fix it in a reliable way. You can also use this feature to change date formats, or import hex encoded blob values. Very useful feature.
此查询按原样将值导入 column1，并修复第 2 列和第 3 列的值。使用此查询，您不必禁用严格模式，而且您实际上可以控制进入数据库的数据，是能够以可靠的方式修复它。您还可以使用此功能更改日期格式，或导入十六进制编码的 blob 值。非常有用的功能。
回答by jeteon
I'm putting this in an answer as I am yet to have the rep to comment on your follow up question about the settings not "holding". It has been my experience with MySQL that the settings in the configuration file are only read on starting the server so that you would have to restart the server for new settings to take effect.
我把这个放在一个答案中，因为我还没有让代表评论你关于设置不“保持”的后续问题。根据我对 MySQL 的经验，配置文件中的设置仅在启动服务器时读取，因此您必须重新启动服务器才能使新设置生效。
The server is the daemon that the command-line client communicates with when you run commands so that restarting the command line client does not actually restart the server itself.
服务器是命令行客户端在您运行命令时与之通信的守护进程，因此重新启动命令行客户端实际上不会重新启动服务器本身。
A more elaborate explanation is provided in this answer: https://serverfault.com/a/79051.
此答案提供了更详细的解释：https: //serverfault.com/a/79051。
