我有两个表具有完全相同的架构.我可以插入一张桌子,但不能插入另一张桌子.一个失败的抱怨没有默认值.这是我的表格创建语句
I have two tables with exactly the same schema. I can insert into one table but not another. The one that fails complains about no default value. Here's my create statement for the table
CREATE TABLE `t_product` ( `product_id` varchar(10) NOT NULL, `prod_name` varchar(150) DEFAULT NULL, `price` decimal(6,2) NOT NULL, `prod_date` date NOT NULL, `prod_meta` varchar(250) DEFAULT NULL, `prod_key` varchar(250) DEFAULT NULL, `prod_desc` varchar(150) DEFAULT NULL, `prod_code` varchar(12) DEFAULT NULL, `prod_price` decimal(6,2) NOT NULL, `prod_on_promo` tinyint(1) unsigned NOT NULL, `prod_promo_sdate` date DEFAULT NULL, `prod_promo_edate` date DEFAULT NULL, `prod_promo_price` decimal(6,2) NOT NULL, `prod_discountable` tinyint(1) unsigned NOT NULL, `prod_on_hold` tinyint(1) unsigned NOT NULL, `prod_note` varchar(150) DEFAULT NULL, `prod_alter` varchar(150) DEFAULT NULL, `prod_extdesc` text, `prod_img` varchar(5) NOT NULL, `prod_min_qty` smallint(6) unsigned NOT NULL, `prod_recent` tinyint(1) unsigned NOT NULL, `prod_name_url` varchar(150) NOT NULL, `upc_code` varchar(50) DEFAULT NULL, PRIMARY KEY (`product_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1当我在database1中运行此语句时,它会成功插入:
When I run this statement in database1, it successfully inserts:
insert into t_product (product_id) values ('jlaihello');当我在database2中运行此确切语句时,出现错误:
When I run this exact statement in database2, I get the error:
ERROR 1364 (HY000): Field 'price' doesn't have a default value为什么此错误仅在database2中发生?据我所知,database1和database2之间的区别是:
Why is this error happening only in database2? As far as I can tell, the difference between database1 and database2 are:
database1使用mysql Ver 14.14 Distrib 5.5.53,用于使用readline 6.3的debian-linux-gnu(i686)
database1 uses mysql Ver 14.14 Distrib 5.5.53, for debian-linux-gnu (i686) using readline 6.3
和
database2使用mysql Ver 14.14 Distrib 5.7.16,适用于Linux(x86_64)(使用EditLine包装器)
database2 uses mysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper
如何使database2的行为类似于database1?
How do I make database2 behave like database1?
编辑 有数百个表受此影响.基本上,我们是将数据库移到新服务器上.我从db1做了一个mysqldump,然后导入了db2. t_product只是受此影响的表之一.我想避免手动修改数百个表的架构.我更喜欢一个简单的开关",它将使db2的行为类似于db1.
EDIT There are hundreds of tables affected by this. Basically we're moving a database over to a new server. And I did a mysqldump from db1, and imported into db2. t_product is just ONE of the tables affected by this. I'd like to avoid manually modifying the schema for the hundreds of tables. I prefer a "simple switch" that will make db2 behave like db1.
推荐答案ERROR 1364 (HY000): Field 'price' doesn't have a default value
price decimal(6,2) NOT NULL,
将价格设置为空或分配默认值
Set price to null or assign a default value
这是由STRICT_TRANS_TABLES SQL模式引起的.
This is caused by the STRICT_TRANS_TABLES SQL mode.
打开phpmyadmin并转到More选项卡,然后选择Variables子菜单.向下滚动以查找sql模式.编辑sql模式并删除STRICT_TRANS_TABLES保存.
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.
OR
您可以在数据库管理工具(例如phpMyAdmin)中运行SQL查询:
You can run an SQL query within your database management tool, such as phpMyAdmin:
-- verify that the mode was previously set: SELECT @@GLOBAL.sql_mode; -- update mode: SET @@GLOBAL.sql_mode= 'YOUR_VALUE';OR
在mysql conf文件中找到如下所示的行:
Find the line that looks like so in the mysql conf file:
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
注释上面的行并重新启动mysql服务器
Comment above line out and restart mysql server
更多推荐
mysql无法插入,因为没有默认值?
发布评论