mysql - MARIADB - 如果没有提供默认值，SQL 能否将 NULL 转换为空字符串？

Question

我有一个包含可为空列的表:

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

我插入一个名称设置为 NULL 的行；

INSERT INTO some_table (id, name) VALUES (1, NULL);
Query OK, 1 row affected (0.02 sec)

SELECT * FROM some_table;
+------+------+
| id   | name |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.01 sec)

如果我将表的名称列更改为不可为空，它显然会将 NULL 转换为空字符串:

ALTER TABLE some_table CHANGE COLUMN name name VARCHAR(255) NOT NULL;
Query OK, 1 row affected, 1 warning (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 1

SELECT * FROM some_table;
+------+------+
| id   | name |
+------+------+
|    1 |      |
+------+------+
1 row in set (0.02 sec)

此时我希望出现异常，告诉我数据集中有 NULL，我不能将列名设置为 NOT NULL。

这是 SQL/MariaDB 中的可配置选项吗？为什么 NULL 被转换为空字符串？

更改表时会调用警告:

SHOW WARNINGS;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.01 sec)

版本:

SELECT version();
+----------------+
| version()      |
+----------------+
| 5.5.62-MariaDB |
+----------------+
1 row in set (0.02 sec)

Answer 1

显然，来自 documentation for ALTER TABLE ，启用严格模式会阻止您的 alter 语句成功:

This conversion may result in alteration of data. For example, if you shorten a string column, values may be truncated. To prevent the operation from succeeding if conversions to the new data type would result in loss of data, enable strict SQL mode before using ALTER TABLE.

从 MySQL 中启用严格模式的一种方法:

SET GLOBAL sql_mode='STRICT_TRANS_TABLES';

See here其他选项。