更新期间MySQL INET_ATON错误，消息为“字符串值不正确". [英] MySQL INET_ATON error during update, message is "Incorrect string value"

问题描述

MySQL是5.7.23.

MySQL is 5.7.23.

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

IFNULL(INET_ATON(''),0) returns a 0 in a plain select, but ERRORS during update ... set assignment

问:是否有一个补丁可以防止ERROR 1411 (HY000): Incorrect string value:

Q: Is there a patch that prevents ERROR 1411 (HY000): Incorrect string value:

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

The update statement is generated by a larger app and it would be hard to modify the app.

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字符串

Update works, real ip string

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

Plain select with IFNULL gets desired 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字符串更新失败

Update fail with IFNULL and empty ip string for 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和INET_ATON的IPv6回送地址更新失败

Update fail with IFNULL and IPv6 loop back addr for 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

推荐答案

实际上，在两种情况下，空字符串都是无效的参数.如果您查看警告，则可以看到这是一个警告:

In fact, the empty string is an invalid argument in both cases. You can see that it's a warning if you view the warnings:

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中使用相同的功能时却是错误.

I'm not sure why this is only a warning when using SELECT but an error when using the same function in UPDATE.

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

A workaround is to use NULL instead of a blank string. INET_ATON() returns NULL without a warning or error if you pass NULL as your IP address string:

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

我了解您说很难更改应用程序，但这是我建议的唯一解决方法.

I understand you said it's hard to change the app, but this is the only workaround I can suggestion.

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

The best solution of course is to avoid passing any invalid string to INET_ATON().

这篇关于更新期间MySQL INET_ATON错误，消息为“字符串值不正确".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！