英文:

MySQL insert varchar value into int auto increment column

问题

以下是您要翻译的内容：

我在项目的暂存服务器上注意到了奇怪的MySQL行为。给定：

```sql
create table test_table
(
    id   int auto_increment,
    name varchar(64) not null,
    constraint test_table_pk
        primary key (id)
);

insert into test_table (id, name)
VALUES (3, 'value 3');
insert into test_table (id, name)
VALUES (5, 'value 5');
insert into test_table (id, name)
VALUES ('value 6 id', 'value 6');

select *
from test_table;

结果：

mysql> select * from test_table;
+----+---------+
| id | name    |
+----+---------+
|  3 | value 3 |
|  5 | value 5 |
|  6 | value 6 |
+----+---------+
2 rows in set (0.01 sec)

前两个插入语句按预期正常工作。第三个插入语句背后的魔法是什么？它能够工作，似乎将不正确类型的值替换为自动递增值。
我正在尝试创建DB服务器的Docker版本。但我找不到启用此行为的配置选项。Docker DB实例拒绝了第三个插入语句并显示错误消息：

Incorrect Integer value: 'value 6 id' for column 'id'

mysql> SHOW VARIABLES LIKE 'version';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| version       | 5.7.27-30 |
+---------------+-----------+
1 row in set (0.01 sec)

更新

在暂存服务器上：

show variables like '%sql_mode%';

返回：

IGNORE_SPACE,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

我的docker-compose.yaml：

version: "3.8"
services:
 db:
    image: percona/percona-server:5.7.27
    volumes:
      -db-data:/var/lib/mysql
    env_file:
      - ./db.env
    command:
      --sql_mode=IGNORE_SPACE,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    ports:
      - "3306:3306"
  flyway:
    image: flyway/flyway:7.15.0
    command: migrate
    volumes:
      - ./../../sql/migrations:/flyway/sql
      - ./flyway/conf:/flyway/conf
    depends_on:
      -db
volumes:
  db-data:

有什么想法吗？
谢谢

<details>
<summary>英文:</summary>

I noticed strange MySQL behaviour on projects staging server. Given:

create table test_table
(
id int auto_increment,
name varchar(64) not null,
constraint test_table_pk
primary key (id)
);

insert into test_table (id, name)
VALUES (3, 'value 3');
insert into test_table (id, name)
VALUES (5, 'value 5');
insert into test_table (id, name)
VALUES ('value 6 id', 'value 6');

select *
from test_table;

Results:

mysql> select * from test_table;
+----+---------+
| id | name |
+----+---------+
| 3 | value 3 |
| 5 | value 5 |
| 6 | value 6 |
+----+---------+
2 rows in set (0.01 sec)

First two insert statements works fine as expected. What's the magic behind the third one? It works and it seems incorrect type value is replaced with auto increment value.
I'm trying to create docker version of the DB server. But I can't find what's config option to enable this behaviour. The third insert statement is rejected by docker DB instance with error message:

Incorrect Integer value: 'value 6 id' for column 'id'

mysql> SHOW VARIABLES LIKE 'version';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| version | 5.7.27-30 |
+---------------+-----------+
1 row in set (0.01 sec)

UPDATE

On Staging server:

show variables like '%sql_mode%';

Returns:

IGNORE_SPACE,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

My docker-compose.yaml

version: "3.8"
services:
db:
image: percona/percona-server:5.7.27
volumes:
-db-data:/var/lib/mysql
env_file:
- ./db.env
command:
--sql_mode=IGNORE_SPACE,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
ports:
- "3306:3306"
flyway:
image: flyway/flyway:7.15.0
command: migrate
volumes:
- ./../../sql/migrations:/flyway/sql
- ./flyway/conf:/flyway/conf
depends_on:
-db
volumes:
db-data:

Any ideas?
Thank you

</details>


# 答案1
**得分**: 2

你已启用了`STRICT_TRANS_TABLES`，这会导致无效数据值被拒绝，应该从列表中移除：

SET sql_mode=&#39;IGNORE_SPACE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION&#39;;

[示例在此处][1]

[1]: https://dbfiddle.uk/zWjNJuM9

<details>
<summary>英文:</summary>

You have enabled `STRICT_TRANS_TABLES` which causes invalid data values to be rejected, this should be removed from the list:

    SET sql_mode=&#39;IGNORE_SPACE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION&#39;;

[Demo here][1]


  [1]: https://dbfiddle.uk/zWjNJuM9

</details>



# 答案2
**得分**: 1

这与sql_mode有关。您可以检查两个数据库实例的sql_mode参数以找出其中的秘密。

`show variables like '％sql_mode％';`

<details>
<summary>英文:</summary>

This has something to do with sql_mode. You can check the sql_mode parameters of two database instances to find out the secrets.

`show variables like &#39;%sql_mode%&#39;;`

</details>