mysql导入无效的默认值_mysql无法插入，因为没有默认值？

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

When I run this statement in database1, it successfully inserts:

insert into t_product (product_id) values ('jlaihello');

When I run this exact statement in database2, I get the error:

ERROR 1364 (HY000): Field 'price' doesn't have a default value

Why is this error happening only in database2? As far as I can tell, the difference between database1 and database2 are:

database1 uses mysql Ver 14.14 Distrib 5.5.53, for debian-linux-gnu (i686) using readline 6.3

and

database2 uses mysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper

How do I make database2 behave like database1?

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

EDIT:

This is caused by the STRICT_TRANS_TABLES SQL mode.

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

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

Find the line that looks like so in the mysql conf file:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Comment above line out and restart mysql server