MySQL 字段“id”没有默认值？

Question

提问by Tomas Albertsson

I am new to this SQL; I have seen similar question with much bigger programs, which I can't understand at the moment. I am making a database for games of cards to use in my homepage.

我是这个 SQL 的新手；我在更大的程序中看到过类似的问题，目前我无法理解。我正在为我的主页中使用的纸牌游戏制作数据库。

I am using MySQL Workbench on Windows. The error I get is:

我在 Windows 上使用 MySQL Workbench。我得到的错误是：

Error Code: 1364. Field 'id' doesn't have a default value

错误代码：1364。字段“id”没有默认值

CREATE TABLE card_games
(
nafnleiks varchar(50), 
leiklysing varchar(3000), 
prentadi varchar(1500), 
notkunarheimildir varchar(1000), 
upplysingar varchar(1000), 
ymislegt varchar(500), 
id int(11) PK
);

insert into card_games (nafnleiks, leiklysing, prentadi, notkunarheimildir, upplysingar, ymislegt)

values('Svartipétur',
'Leiklysingu vantar',
'Er prentae í: Tórarinn Guemundsson (2010). Spilabókin - Allir helstu spilaleikir og spil.',
'Heimildir um notkun: árni Siguresson (1951). Hátíeir og skemmtanir fyrir hundrae árum',
'Aerar upplysingar',
'ekkert hér sem stendur'
);

values('Handkurra',
'Leiklysingu vantar',
'Er prentae í: Tórarinn Guemundsson (2010). Spilabókin - Allir helstu spilaleikir og spil.',
'Heimildir um notkun', 
'Aerar upplysingar',
'ekkert her sem stendur'
);

values('Veieimaeur',
'Leiklysingu vantar',
'Tórarinn Guemundsson (2010). Spilabókin - Allir helstu spilaleikir og spil. Reykjavík: Bókafélagie. Bls. 19-20.',
'vantar',
'vantar',
'vantar'
);

Answer 1

回答by fedorqui 'SO stop harming'

As idis the primary key, you cannot have different rows with the same value. Try to change your table so that the idis auto incremented:

由于id是主键，你不能有相同的值不同的行。尝试更改您的表格，以便id自动递增：

id int NOT NULL AUTO_INCREMENT

and then set the primary key as follows:

然后设置主键如下：

PRIMARY KEY (id)

All together:

全部一起：

CREATE TABLE card_games (
   id int(11) NOT NULL AUTO_INCREMENT,
   nafnleiks varchar(50),
   leiklysing varchar(3000), 
   prentadi varchar(1500), 
   notkunarheimildir varchar(1000),
   upplysingar varchar(1000),
   ymislegt varchar(500),
   PRIMARY KEY (id));

Otherwise, you can indicate the idin every insertion, taking care to set a different value every time:

否则，您可以id在每次插入时指定，注意每次设置不同的值：

insert into card_games (id, nafnleiks, leiklysing, prentadi, notkunarheimildir, upplysingar, ymislegt)

values(1, 'Svartipétur', 'Leiklysingu vantar', 'Er prentae í: Tórarinn Guemundsson (2010). Spilabókin - Allir helstu spilaleikir og spil.', 'Heimildir um notkun: árni Siguresson (1951). Hátíeir og skemmtanir fyrir hundrae árum', 'Aerar upplysingar', 'ekkert hér sem stendur' );

Answer 2

回答by Renish Aghera

There are 2 solutions mentioned below:

下面提到了2个解决方案：

Solution 1

解决方案1

MySQL is most likely in STRICT SQL mode. Try to execute SQL query SET GLOBAL sql_mode=''or edit your my.cnf / my.ini to make sure you aren't setting STRICT_ALL_TABLESand/or STRICT_TRANS_TABLES.

MySQL 最有可能处于STRICT SQL 模式。尝试执行 SQL 查询SET GLOBAL sql_mode=''或编辑 my.cnf / my.ini 以确保您没有设置STRICT_ALL_TABLES和/或STRICT_TRANS_TABLES.

Solution 2

解决方案2

If Solution-1 is not working then try Solution-2 as given in below steps:

如果解决方案 1 不起作用，请按照以下步骤尝试解决方案 2：

Run MySQL Administrator toolas Administrator.
Then go to Startup Variable.
Then go to the Advance tab.
find SQL Mode and remove the STRICT_ALL_TABLESand/or STRICT_TRANS_TABLESand then Click on Apply Changes.
Restart MySQL Server.
Done.

以管理员身份运行MySQL Administrator 工具。
然后转到启动变量。
然后转到高级选项卡。
找到 SQL 模式并删除STRICT_ALL_TABLES和/或STRICT_TRANS_TABLES然后单击应用更改。
重新启动 MySQL 服务器。
完毕。

Note:I have tested these solutions in MySQL Server 5.7

注意：我已经在 MySQL Server 5.7 中测试了这些解决方案

Answer 3

回答by John Joe

The id should set as auto-increment.

id 应该设置为auto-increment.

To modify an existing id column to auto-increment, just add this

要将现有的 id 列修改为自动递增，只需添加此

ALTER TABLE card_games MODIFY id int NOT NULL AUTO_INCREMENT;

Answer 4

回答by hardik patel

I was getting error while ExecuteNonQuery() resolved with adding AutoIncrement to Primary Key of my table. In your case if you don't want to add primary key then we must need to assign value to primary key.

通过将 AutoIncrement 添加到我的表的主键来解决 ExecuteNonQuery() 时出现错误。在您的情况下，如果您不想添加主键，那么我们必须为主键分配值。

ALTER TABLE `t1` 
CHANGE COLUMN `id` `id` INT(11) NOT NULL AUTO_INCREMENT ;

Answer 5

回答by Vivek Pal

make sure that you do not have defined setter for the for your primary key in model class.

确保您没有为模型类中的主键定义 setter。

public class User{
@Id
@GeneratedValues
private int user_Id;
private String userName;

public int getUser_Id{
return user_Id;
}

public String getUserName{
return userName;
}

public void setUserName{
this.userName=userName;
}
}

Answer 6

回答by radoo

Since mysql 5.6, there is a new default that makes sure you are explicitly inserting every field that doesn't have a default value set in the table definition.

从 mysql 5.6 开始，有一个新的默认值，可确保您显式插入在表定义中没有设置默认值的每个字段。

to disable and test this: see this answer here: mysql error 1364 Field doesn't have a default values

禁用和测试这个：在这里看到这个答案：mysql error 1364 Field does not have a default values

I would recommend you test without it, then reenable it and make sure all your tables have default values for fields you are not explicitly passing in every INSERT query.

我建议您在没有它的情况下进行测试，然后重新启用它并确保您的所有表都具有您没有在每个 INSERT 查询中明确传递的字段的默认值。

If a third party mysql viewer is giving this error, you are probably limited to the fix in that link.

如果第三方 mysql 查看器出现此错误，则您可能仅限于该链接中的修复程序。

Answer 7

回答by joseantgv

This is caused by MySQL having a strict mode set which won't allow INSERT or UPDATE commands with empty fields where the schema doesn't have a default value set.

这是由于 MySQL 具有严格的模式集，该模式集不允许具有空字段的 INSERT 或 UPDATE 命令，其中架构没有设置默认值。

There are a couple of fixes for this.

对此有几个修复程序。

First ‘fix' is to assign a default value to your schema. This can be done with a simple ALTER command:

第一个“修复”是为您的架构分配一个默认值。这可以通过一个简单的 ALTER 命令来完成：

ALTER TABLE `details` CHANGE COLUMN `delivery_address_id` `delivery_address_id` INT(11) NOT NULL DEFAULT 0 ;

However, this may need doing for many tables in your database schema which will become tedious very quickly. The second fix is to remove sql_mode STRICT_TRANS_TABLES on the mysql server.

但是，这可能需要对数据库模式中的许多表进行，这将很快变得乏味。第二个修复是删除 mysql 服务器上的 sql_mode STRICT_TRANS_TABLES。

If you are using a brew installed MySQL you should edit the my.cnf file in the MySQL directory. Change the sql_mode at the bottom:

如果您使用 brew 安装的 MySQL，您应该编辑 MySQL 目录中的 my.cnf 文件。更改底部的 sql_mode：

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
sql_mode=NO_ENGINE_SUBSTITUTION

Save the file and restart Mysql.

保存文件并重启Mysql。

Source: https://www.euperia.com/development/mysql-fix-field-doesnt-default-value/1509

来源：https: //www.euperia.com/development/mysql-fix-field-doesnt-default-value/1509

Answer 8

回答by biniam

Solution: Remove STRICT_TRANS_TABLESfrom sql_mode

解决方法：STRICT_TRANS_TABLES从sql_mode

To check your default setting,

要检查您的默认设置，

mysql> set @@sql_mode = 
'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode                                                     |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

Run a sample query

运行示例查询

mysql> INSERT INTO nb (id) VALUES(3);
ERROR 1364 (HY000): Field 'field' doesn't have a default value

Remove your STRICT_TRANS_TABLESby resetting it to null.

STRICT_TRANS_TABLES通过将其重置为空来删除您的。

mysql> set @@sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

Now, run the same test query.

现在，运行相同的测试查询。

mysql> INSERT INTO nb (id) VALUES(3);
Query OK, 1 row affected, 1 warning (0.00 sec)

Source: https://netbeans.org/bugzilla/show_bug.cgi?id=190731

来源：https: //netbeans.org/bugzilla/show_bug.cgi?id=190731

Answer 9

回答by user158

For me the issue got fixed when I changed

对我来说，当我改变时，问题得到了解决

<id name="personID" column="person_id">
    <generator class="native"/>
</id>

to

到

<id name="personID" column="person_id">
    <generator class="increment"/>
</id>

in my Person.hbm.xml.

在我的Person.hbm.xml.

after that I re-encountered that same error for an another field(mobno). I tried restarting my IDE, recreating the database with previous back issue got eventually fixed when I re-create my tables using (without ENGINE=InnoDB DEFAULT CHARSET=latin1;and removing underscores in the field name)

在那之后，我在另一个领域（mobno）再次遇到了同样的错误。我尝试重新启动我的 IDE，当我使用重新创建我的表时，重新创建具有先前返回问题的数据库最终得到修复（没有ENGINE=InnoDB DEFAULT CHARSET=latin1;并删除字段名称中的下划线）

CREATE TABLE `tbl_customers` (
  `pid` bigint(20) NOT NULL,
  `title` varchar(4) NOT NULL,
  `dob` varchar(10) NOT NULL,
  `address` varchar(100) NOT NULL,
  `country` varchar(4) DEFAULT NULL,
  `hometp` int(12) NOT NULL,
  `worktp` int(12) NOT NULL,
  `mobno` varchar(12) NOT NULL,
  `btcfrom` varchar(8) NOT NULL,
  `btcto` varchar(8) NOT NULL,
  `mmname` varchar(20) NOT NULL
)

instead of

代替

CREATE TABLE `tbl_person` (
  `person_id` bigint(20) NOT NULL,
  `person_nic` int(10) NOT NULL,
  `first_name` varchar(20) NOT NULL,
  `sur_name` varchar(20) NOT NULL,
  `person_email` varchar(20) NOT NULL,
  `person_password` varchar(512) NOT NULL,
  `mobno` varchar(10) NOT NULL DEFAULT '1',
  `role` varchar(10) NOT NULL,
  `verified` int(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I probably think this due to usingENGINE=InnoDB DEFAULT CHARSET=latin1;, because I once got the error org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Unknown column 'mob_no' in 'field list'even though it was my previous column name, which even do not exist in my current table. Even after backing up the database(with modified column name, using InnoDB engine) I still got that same error with old field name. This probably due to caching in that Engine.

我可能认为这是由于使用ENGINE=InnoDB DEFAULT CHARSET=latin1;，因为org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Unknown column 'mob_no' in 'field list'即使它是我以前的列名，我也曾经遇到过错误，它甚至不存在于我当前的表中。即使在备份数据库（使用修改的列名，使用 InnoDB 引擎）之后，我仍然遇到与旧字段名相同的错误。这可能是由于该引擎中的缓存。

Answer 10

回答by Andrew Wood

I had an issue on AWS with mariadb - This is how I solved the STRICT_TRANS_TABLES issue

我在使用 mariadb 的 AWS 上遇到了问题 - 这就是我解决 STRICT_TRANS_TABLES 问题的方法

SSH into server and chanege to the ect directory

SSH 进入服务器并更改到 ect 目录

[ec2-user]$ cd /etc

Make a back up of my.cnf

备份 my.cnf

[ec2-user etc]$ sudo cp -a my.cnf{,.strict.bak}

I use nano to edit but there are others

我使用 nano 进行编辑，但还有其他的

[ec2-user etc]$ sudo nano my.cnf

Add this line in the the my.cnf file

在 my.cnf 文件中添加这一行

#
#This removes STRICT_TRANS_TABLES
#
sql_mode=""

Then exit and save

然后退出并保存

OR if sql_mode is there something like this:

或者，如果 sql_mode 是这样的：

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Change to

改成

sql_mode=""

exit and save

退出并保存

then restart the database

然后重启数据库

[ec2-user etc]$ sudo systemctl restart mariadb

MySQL 字段“id”没有默认值？

提问by Tomas Albertsson

回答by fedorqui 'SO stop harming'

回答by Renish Aghera

Solution 1

解决方案1

Solution 2

解决方案2

回答by John Joe

回答by hardik patel

回答by Vivek Pal

回答by radoo

回答by joseantgv

回答by biniam

回答by user158

回答by Andrew Wood

相关推荐

最近更新

标签

MySQL 字段“id”没有默认值？

提问by Tomas Albertsson

回答by fedorqui 'SO stop harming'

回答by Renish Aghera

Solution 1

解决方案1

Solution 2

解决方案2

回答by John Joe

回答by hardik patel

回答by Vivek Pal

回答by radoo

回答by joseantgv

回答by biniam

回答by user158

回答by Andrew Wood

相关推荐

MySQL SQL - 转换日期时间格式

MySQL 没有时间戳的 HQL 中的日期比较

MySQL 如果表中的一行不存在，我如何更新或插入它？

MySQL 将 NOT NULL 约束添加到列

相关推荐

最近更新

标签