字段'id'没有默认值？

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',
'Leiklýsingu vantar',
'Er prentað í: Þórarinn Guðmundsson (2010). Spilabókin - Allir helstu spilaleikir og spil.',
'Heimildir um notkun: Árni Sigurðsson (1951). Hátíðir og skemmtanir fyrir hundrað árum',
'Aðrar upplýsingar',
'ekkert hér sem stendur'
);

values('Handkurra',
'Leiklýsingu vantar',
'Er prentað í: Þórarinn Guðmundsson (2010). Spilabókin - Allir helstu spilaleikir og spil.',
'Heimildir um notkun', 
'Aðrar upplýsingar',
'ekkert her sem stendur'
);

values('Veiðimaður',
'Leiklýsingu vantar',
'Þórarinn Guðmundsson (2010). Spilabókin - Allir helstu spilaleikir og spil. Reykjavík: Bókafélagið. Bls. 19-20.',
'vantar',
'vantar',
'vantar'
);

9 个解决方案

#1

As id is the primary key, you cannot have different rows with the same value. Try to change your table so that the id is 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 id in 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', 'Leiklýsingu vantar', 'Er prentað í: Þórarinn Guðmundsson (2010). Spilabókin - Allir helstu spilaleikir og spil.', 'Heimildir um notkun: Árni Sigurðsson (1951). Hátíðir og skemmtanir fyrir hundrað árum', 'Aðrar upplýsingar', 'ekkert hér sem stendur' );

#2

There are 2 solutions mentioned below:

下面提到了两种解决方案：

Solution 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_TABLES and/or STRICT_TRANS_TABLES.

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

Solution 2

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

如果Solution-1无法正常工作，请尝试按以下步骤给出的Solution-2：

1. Run MySQL Administrator tool as Administrator.
2. 以管理员身份运行MySQL Administrator工具。
3. Then go to Startup Variable.
4. 然后转到启动变量。
5. Then go to the Advance tab.
6. 然后转到“高级”选项卡。
7. find SQL Mode and remove the STRICT_ALL_TABLES and/or STRICT_TRANS_TABLES and then Click on Apply Changes.
8. 找到SQL模式并删除STRICT_ALL_TABLES和/或STRICT_TRANS_TABLES，然后单击Apply Changes。
9. Restart MySQL Server.
10. 重启MySQL服务器。
11. Done.
12. 完成。

Note: I have tested these solutions in MySQL Server 5.7

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

#3

The id should set as auto-increment.

id应设置为自动增量。

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

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

ALTER TABLE card_games MODIFY id int NOT NULL AUTO_INCREMENT;

#4

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;
}
}

#5

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错误1364字段没有默认值

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查看器出现此错误，您可能仅限于该链接中的修复程序。

#6

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

#7

Solution: Remove STRICT_TRANS_TABLES from sql_mode

解决方案：从sql_mode中删除STRICT_TRANS_TABLES

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_TABLES by resetting it to null.

通过将STRICT_TRANS_TABLES重置为null来删除它。

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

#8

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 using ENGINE=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 - '字段列表'中的未知列'mob_no'，即使它是我之前的列名，甚至在我当前的表中也不存在。即使在备份数据库（使用修改的列名，使用InnoDB引擎）之后，我仍然使用旧字段名称得到了相同的错误。这可能是由于该引擎中的缓存。

#9

As a developer it is highly recommended to use STRICT mode because it will allow you to see issues/errors/warnings that may come about instead of just working around it by turning off strict mode. It's also better practice.

作为开发人员，强烈建议使用STRICT模式，因为它允许您查看可能出现的问题/错误/警告，而不是通过关闭严格模式来解决它。这也是更好的做法。

Strict mode is a great tool to see messy, sloppy code.

严格模式是一个很好的工具，可以看到凌乱，邋code的代码。