mysql无法插入，因为没有默认值? [英] mysql can not insert because no default value?

问题描述

我有两个表具有完全相同的架构.我可以插入一张桌子，但不能插入另一张桌子.一个失败的抱怨没有默认值.这是我的表格创建语句

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无法插入，因为没有默认值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！