mysql 错误 1364 字段没有默认值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15438840/
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
mysql error 1364 Field doesn't have a default values
提问by kk1957
My table looks like
我的桌子看起来像
create table try ( name varchar(8), CREATED_BY varchar(40) not null);
and then I have a trigger to auto populate the CREATED_BY field
然后我有一个触发器来自动填充 CREATED_BY 字段
create trigger autoPopulateAtInsert BEFORE INSERT on try for each row set new.CREATED_BY=user();
When I do an insert using
当我使用
insert into try (name) values ('abc');
the entry is made in the table but I still get the error message
该条目已在表中进行，但我仍然收到错误消息
Field 'CREATED_BY' doesn't have a default value Error no 1364
Is there a way to suppress this error without making the field nullable AND without removing the triggfer? Otherwise my hibernate will see these exceptions ( even though the insertions have been made) and then application will crash.
有没有办法在不使字段为空且不删除触发器的情况下抑制此错误？否则我的休眠会看到这些异常（即使已经进行了插入），然后应用程序将崩溃。
采纳答案by KinSlayerUY
Set a default value for Created_By(eg: empty VARCHAR) and the trigger will update the value anyways.
为Created_By(eg: empty VARCHAR)设置默认值，触发器无论如何都会更新该值。
create table try (
name varchar(8),
CREATED_BY varchar(40) DEFAULT '' not null
);
回答by Phyxx
This is caused by the STRICT_TRANS_TABLESSQL mode defined in the
这是由STRICT_TRANS_TABLES定义的SQL 模式引起的
%PROGRAMDATA%\MySQL\MySQL Server 5.6\my.ini
%PROGRAMDATA%\MySQL\MySQL 服务器 5.6\my.ini
file. Removing that setting and restarting MySQL should fix the problem.
文件。删除该设置并重新启动 MySQL 应该可以解决问题。
If editing that file doesn't fix the issue, see http://dev.mysql.com/doc/refman/5.6/en/option-files.htmlfor other possible locations of config files.
如果编辑该文件不能解决问题，请参阅http://dev.mysql.com/doc/refman/5.6/en/option-files.html以了解配置文件的其他可能位置。
回答by Nilesh Dhangare
Open phpmyadmin and goto 'More' Tab and select 'Variables' submenu. Scroll down to find sql mode. Edit sql mode and remove 'STRICT_TRANS_TABLES' Save it.
打开 phpmyadmin 并转到“更多”选项卡并选择“变量”子菜单。向下滚动以找到 sql 模式。编辑 sql 模式并删除 'STRICT_TRANS_TABLES' 保存。
回答by Kamil
In phpmyadmin, perform the following:
在 phpmyadmin 中，执行以下操作：
select @@GLOBAL.sql_mode
In my case, I get the following:
就我而言，我得到以下信息：
ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES ,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Copy this result and remove STRICT_TRANS_TABLES. Then perform the following:
复制此结果并删除STRICT_TRANS_TABLES. 然后执行以下操作：
set GLOBAL sql_mode='ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
回答by Kingsley Ijomah
When I had this same problem with mysql5.6.20 installed with Homebrew, I solved it by going into my.cnf
当我用 Homebrew 安装 mysql5.6.20 遇到同样的问题时，我通过进入 my.cnf 解决了它
nano /usr/local/Cellar/mysql/5.6.20_1/my.cnf
Find the line that looks like so:
找到看起来像这样的行：
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
Comment above line out and restart mysql server
注释上面的行并重新启动 mysql 服务器
mysql.server restart
Error gone!
错误消失！
回答by MilanG
Run mysql console:
运行 mysql 控制台：
mysql -u your_username -p
, select database:
, 选择数据库：
USE your_database;
and run (also from mysql console):
并运行（也从 mysql 控制台）：
SET GLOBAL sql_mode='';
That will turn off strict mode and mysql won't complain any more.
这将关闭严格模式，mysql 不会再抱怨了。
To make things clear: your database definition says "this field must have default value defined", and by doing steps from above you say to MySql "neah, just ignore it". So if you just want to do some quick fix locally this solution is ok. But generally you should investigate in your database definition and check if field really needs default value and if so set it. And if default value is not needed this requirement should be removed to have clean situation.
说清楚一点：你的数据库定义说“这个字段必须定义默认值”，通过上面的步骤你对MySql说“不，忽略它”。因此，如果您只想在本地进行一些快速修复，则此解决方案是可以的。但通常你应该调查你的数据库定义并检查字段是否真的需要默认值，如果需要设置它。如果不需要默认值，则应删除此要求以获得干净的情况。
回答by Damjan Pavlica
As others said, this is caused by the STRICT_TRANS_TABLESSQL mode.
正如其他人所说，这是由STRICT_TRANS_TABLESSQL 模式引起的。
To check whether STRICT_TRANS_TABLESmode is enabled:
要检查STRICT_TRANS_TABLES模式是否已启用：
SHOW VARIABLES LIKE 'sql_mode';
To disable strict mode:
要禁用严格模式：
SET GLOBAL sql_mode='';
回答by Vinith
Before every insert action I added below line and solved my issue,
在我在下面添加的每个插入操作之前并解决了我的问题，
SET SQL_MODE = '';
I'm not sure if this is the best solution,
我不确定这是否是最好的解决方案，
SET SQL_MODE = ''; INSERT INTO `mytable` ( `field1` , `field2`) VALUES ('value1', 'value2');
回答by Devraj Gupta
Its work and tested Copy to Config File: /etc/mysql/my.cnf OR /bin/mysql/my.ini
它的工作和测试复制到配置文件：/etc/mysql/my.cnf OR /bin/mysql/my.ini
[mysqld]
port = 3306
sql-mode=""
then restart MySQL
然后重启MySQL
回答by Rohit Dhiman
Modify your query and add "IGNORE" as:
修改您的查询并将“忽略”添加为：
INSERT IGNORE INTO `mytable` ( `field1` , `field2`) VALUES ('value1', 'value2');
