mysql无法插入，因为没有默认值?

编程入门行业动态更新时间:2024-10-28 15:23:37

本文介绍了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

当我在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.

您可以在数据库管理工具(例如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';