MySQL更新时出现INET_ATON错误，“Incorrect string value”是提示信息。

Question

MySQL版本为5.7.23。

IFNULL(INET_ATON(''),0) 在普通的查询中返回0，但在 update ... set 赋值时会出现错误。

问：是否有修补程序可以避免出现ERROR 1411 (HY000): Incorrect string value:？

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

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和空的IP字符串更新INET_ATON操作失败

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

使用IFNULL和IPv6环回地址更新INET_ATON时失败

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

- Richard

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()。

- Bill Karwin

Answer 2

感谢你的见解，比尔。

结果发现，在应用程序中只有十几个地方使用了 INET_ATON，因此我要推动管理员让我将这些地方更改为 APP_INET_ATON，仅当IP_STRING是“合法”的时候才会调用系统的 INET_ATON。

DROP FUNCTION IF EXISTS APP_INET_ATON;
CREATE FUNCTION APP_INET_ATON(IP_STRING VARCHAR(50)) RETURNS bigint DETERMINISTIC
  RETURN 
    CASE 
      WHEN IP_STRING IS NULL THEN 0
      WHEN IP_STRING NOT REGEXP '^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$' THEN 0
      ELSE INET_ATON (IP_STRING)
    END;

找出“坏”的IP_STRING值来自哪里，这是另一天的事情。

- Richard