更新期间 MySQL INET_ATON 错误，消息为 "Incorrect string value"

Question

MySQL 是 5.7.23。

IFNULL(INET_ATON(''),0) 在普通选择中返回 0，但在 update ... set 赋值期间出现错误

问:是否有补丁可以防止ERROR 1411 (HY000):不正确的字符串值:

更新语句是由较大的应用程序生成的，很难修改应用程序。

mysql> create table foo (id int not null, ip int not null);
Query OK, 0 rows affected (0.21 sec)

mysql> insert into foo values (0,0);
Query OK, 1 row affected (0.26 sec)

更新成功，真实ip字符串

mysql> update foo set ip = ifnull(inet_aton('10.10.10.254'), 0) where id=0;
Query OK, 1 row affected (0.25 sec)
Rows matched: 1  Changed: 1  Warnings: 0

使用 IFNULL 的普通选择得到所需的 0

mysql> select IFNULL(inet_aton(''),0);
+-------------------------+
| IFNULL(inet_aton(''),0) |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

更新失败，IFNULL 且 INET_ATON 的 IP 字符串为空

mysql> update foo set ip = ifnull(inet_aton(''), 0) where id=0;
ERROR 1411 (HY000): Incorrect string value: '''' for function inet_aton

更新因 IFNULL 和 INET_ATON 的 IPv6 环回地址而失败

mysql> update foo set ip = ifnull(inet_aton('::1'), 0) where id=0;
ERROR 1411 (HY000): Incorrect string value: ''::1'' for function inet_aton

Answer 1

事实上，空字符串在这两种情况下都是无效参数。如果您查看警告，您会发现这是一个警告:

mysql> warnings;
Show warnings enabled.

mysql> select IFNULL(inet_aton(''),0);
+-------------------------+
| IFNULL(inet_aton(''),0) |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1411): Incorrect string value: '''' for function inet_aton

我不确定为什么这只是使用 SELECT 时的警告，而在 UPDATE 中使用相同函数时却出现错误。

解决方法是使用 NULL 而不是空白字符串。如果您将 NULL 作为 IP 地址字符串传递，则 INET_ATON() 返回 NULL，且不会出现警告或错误:

mysql> update foo set ip = ifnull(inet_aton(''), 0) where id=0;
ERROR 1411 (HY000): Incorrect string value: '''' for function inet_aton

mysql> update foo set ip = ifnull(inet_aton(null), 0) where id=0;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

我知道您说更改应用程序很困难，但这是我可以建议的唯一解决方法。

最好的解决方案当然是避免将任何无效字符串传递给 INET_ATON()。