令人惊讶的是（至少对我来说），以下查询将插入记录。
CREATE TABLE null_1 (
id INT NOT NULL,
text1 VARCHAR(32) NOT NULL,
text2 VARCHAR(32) NOT NULL DEFAULT 'foo'
);
INSERT INTO null_1 (id) VALUES(1);
INSERT INTO null_1 (text1) VALUES('test');
mysql> SELECT * FROM null_1;
+----+-------+-------+
| id | text1 | text2 |
+----+-------+-------+
| 1 | | foo |
| 0 | test | foo |
+----+-------+-------+
2 rows in set (0.00 sec)
我在 MySQL 官方文档中找不到这种行为的描述，但它被描述为http://sql-info.de/mysql/gotchas.html#1_1
这里，MySQL 在第一行的 text1 列中插入了一个空字符串，在第二行的 id 列中插入了零，尽管每一列都定义为 NOT NULL，没有默认值。由于 INSERT 语句中没有提供任何值，因此可以认为这些是试图在这些列中插入隐式 NULL，这通常会导致语句失败。
... 如果未为列指定 DEFAULT 值，MySQL 会自动分配一个，如下所示。如果列可以采用 NULL 作为值，则默认值为 NULL。如果列声明为 NOT NULL，则默认值取决于列类型：...
是否可以配置 MySQL 以防止这样做，并根据NOT NULL约束拒绝查询？我的 my.cnf 文件如下所示。
## _______________________________________________________________________
## / Rackspace MySQL 5.5 Terse Configuration File \
## | |
## | This is a base configuration file containing the most frequently used |
## | settings with reasonably defined default values for configuring and |
## | tuning MySQL. Note that these settings can likely be further tuned in |
## | order to get optimum performance from MySQL based upon the database |
## | configuration and hardware platform. |
## | |
## | While the settings provided are likely sufficient for most |
## | situations, an exhaustive list of settings (with descriptions) can be |
## | found at: |
## | http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html |
## | |
## | Take care to only add/remove/change a setting if you are comfortable |
## | doing so! For Rackspace customers, if you have any questions or |
## | concerns, please contact the MySQL Database Services Team. Be aware |
## | that some work performed by this team can involve additional billable |
## \ fees. /
## -----------------------------------------------------------------------
## \ ^__^
## \ (oo)\_______
## (__)\ )\/\
## ||----w |
## || ||
[mysqld]
## General
datadir = /var/lib/mysql
tmpdir = /var/lib/mysqltmp
socket = /var/lib/mysql/mysql.sock
skip-name-resolve
sql-mode = NO_ENGINE_SUBSTITUTION
#event-scheduler = 1
## Cache
thread-cache-size = 16
table-open-cache = 4096
table-definition-cache = 2048
query-cache-size = 32M
query-cache-limit = 1M
## Per-thread Buffers
sort-buffer-size = 1M
read-buffer-size = 1M
read-rnd-buffer-size = 1M
join-buffer-size = 1M
## Temp Tables
tmp-table-size = 32M
max-heap-table-size = 64M
## Networking
back-log = 100
#max-connections = 200
max-connect-errors = 10000
max-allowed-packet = 16M
interactive-timeout = 3600
wait-timeout = 600
### Storage Engines
#default-storage-engine = InnoDB
innodb = FORCE
## MyISAM
key-buffer-size = 64M
myisam-sort-buffer-size = 128M
## InnoDB
#innodb-buffer-pool-size = 128M
#innodb-log-file-size = 100M
#innodb-log-buffer-size = 8M
#innodb-file-per-table = 1
#innodb-open-files = 300
## Replication
server-id = 1
#log-bin = /var/log/mysql/bin-log
#relay-log = /var/log/mysql/relay-log
relay-log-space-limit = 16G
expire-logs-days = 7
#read-only = 1
#sync-binlog = 1
#log-slave-updates = 1
#binlog-format = STATEMENT
#auto-increment-offset = 1
#auto-increment-increment = 2
## Logging
#log-output = FILE
#slow-query-log = 1
#slow-query-log-file = /var/log/mysql/slow-log
#log-slow-slave-statements
#long-query-time = 2
#log = /home/mysql_log/allqueries.log
general_log = on
general_log_file=/home/mysql_log/allqueries.log
symbolic-links=0
[mysqld_safe]
log-error = /var/log/mysqld.log
open-files-limit = 65535
innodb_flush_log_at_trx_commit = 0
innodb_strict_mode=on
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE
[mysql]
no-auto-rehash
答案1
我在官方 MySQL 文档中找不到此行为的描述
花了几分钟查看 MySQL 文档，发现这：
Column values can be given in several ways:
If you are not running in strict SQL mode, any column
not explicitly given a value is set to its default
(explicit or implicit) value. For example, if you specify a
column list that does not name all the columns in the
table, unnamed columns are set to their default values.
Default value assignment is described in Section 11.6,
“Data Type Default Values”. See also Section 1.8.3.3,
“Constraints on Invalid Data”.
If you want an INSERT statement to generate an error unless
you explicitly specify values for all columns that do not
have a default value, you should use strict mode. See
Section 5.1.7, “Server SQL Modes”.
答案2
使用 SQL 模式 STRICT_ALL_TABLEShttp://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_strict_all_tables。