MySQL 如何修复“变量'sql_mode'无法设置为'NULL'的值”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5773570/
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
How to fix "Variable 'sql_mode' can't be set to the value of 'NULL'" error
提问by markzzz
I have this table :
我有这张桌子：
# Dumping structure for table editz.to_import
CREATE TABLE IF NOT EXISTS `to_import` (
`id` int(11) unsigned NOT NULL auto_increment,
`reference` int(11) unsigned NOT NULL,
`trackid` int(11) unsigned NOT NULL,
`side_pos1` char(2) NOT NULL,
`side1` varchar(255) NOT NULL,
`pos1` char(2) NOT NULL,
`hh1` char(2) NOT NULL,
`mm1` char(2) NOT NULL,
`ss1` char(2) NOT NULL,
`atl1` varchar(255) NOT NULL,
`side_pos2` char(2) NOT NULL,
`side2` varchar(255) NOT NULL,
`pos2` char(2) NOT NULL,
`hh2` char(2) NOT NULL,
`mm2` char(2) NOT NULL,
`ss2` char(2) NOT NULL,
`atl2` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1311 DEFAULT CHARSET=utf8;
# Dumping data for table editz.to_import: ~1.025 rows (approximately)
DELETE FROM `to_import`;
/*!40000 ALTER TABLE `to_import` DISABLE KEYS */;
INSERT INTO `to_import` (`id`, `reference`, `trackid`, `side_pos1`, `side1`, `pos1`, `hh1`, `mm1`, `ss1`, `atl1`, `side_pos2`, `side2`, `pos2`, `hh2`, `mm2`, `ss2`, `atl2`) VALUES
(1, 205, 550, '0', 'Single Side', '0', '??', '??', '??', 'Noizefworer - Tons Of Bluesteel - Special Forces', '0', 'Single Side', '0', '??', '??', '??', 'Noizefworer - Tons Of Bluesteel - Special Forces'),
... some lines, approx 1300)...
(1310, 268, 463, '#', '', '20', '00', '41', '00', 'Ingler - Trek', '#', '', '20', '00', '41', '00', 'Ingler - Trek');
/*!40000 ALTER TABLE `to_import` ENABLE KEYS */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
but when I try to import using phpMyAdmin, I get this message :
但是当我尝试使用 phpMyAdmin 导入时，我收到以下消息：
#1231 - Variable 'sql_mode' can't be set to the value of 'NULL'
Why? And How can I fix this troubles? I'm using HeidiSql 6 for export that table...
为什么？我该如何解决这个问题？我正在使用 HeidiSql 6 导出该表...
In fact the table and the data is added on my db, Just I don't understand why that message...
事实上，表和数据已添加到我的数据库中，只是我不明白为什么该消息...
采纳答案by Travis
I've not used phpMyAdmin but perhaps it has something to do with actually declaring the sql mode for the database?
我没有使用过 phpMyAdmin 但也许它与实际声明数据库的 sql 模式有关？
The SQL modes define what syntax the database should support and what validation checks it should do.
SQL 模式定义了数据库应该支持的语法以及它应该执行的验证检查。
In Mysql from the command line you can check what the sql mode is declared as by:
在命令行中的 Mysql 中，您可以通过以下方式检查 sql 模式声明为：
mysql> SELECT @@sql_mode;
回答by steveayre
If the code you posted is the entire code you're running, then the cause would be:
如果您发布的代码是您正在运行的整个代码，那么原因将是：
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
Normally you'd also have this at the top of a SQL dump to preserve the original value before the dump:
通常，您还会在 SQL 转储的顶部放置它以在转储之前保留原始值：
/*!40101 SET OLD_SQL_MODE=@SQL_MODE */;
In what you posted this is omitted, so @OLD_SQL_MODE is NULL, so at the end you'd be setting it to NULL.
在您发布的内容中省略了此内容，因此@OLD_SQL_MODE 为 NULL，因此最后您将其设置为 NULL。
回答by Sunflowers
Had a similar issue after restarting the import of a large file to MySQL that had timed out. The explanation in this link seems to fit. In my case, all seems okay as per the answer that I found. Note, I was not using Drupal.
重新启动将一个大文件导入 MySQL 后出现了类似的问题，但该问题已超时。此链接中的解释似乎很合适。就我而言，根据我找到的答案，一切似乎都很好。请注意，我没有使用 Drupal。
回答by amit_saxena
None of the above solutions fixed it for me. In case the above (starts with CREATE TABLE) is your dump file, you'll need to add the following above the CREATE TABLE command:
上述解决方案都没有为我修复它。如果上述（以 CREATE TABLE 开头）是您的转储文件，您需要在 CREATE TABLE 命令上方添加以下内容：
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
PS: A good way to debug it is create a fresh dump of your existing DB and see what's different between the two files. Try adding those statements and it should fix it.
PS：调试它的一个好方法是创建现有数据库的新转储并查看两个文件之间的不同之处。尝试添加这些语句，它应该会修复它。
回答by Aryashree Pritikrishna
You can try the below configuration in "\mysql\bin\my.ini" and it will fix the issue.
您可以在“\mysql\bin\my.ini”中尝试以下配置，它将解决问题。
[mysqld]
max_allowed_packet = 64M
default_time_zone="+00:00"
