如何重写我的MySQL更新语句以消除“截断的错误INTEGER值”警告？

I'm using MySQL 5.5.37. I want to eliminate the warnings from my update statement, which are shown below ...

我正在使用MySQL 5.5.37。我想从我的更新语句中消除警告,如下所示......

update resource r 
    set grade_id = convert(substring_index(substring_index(
                   r.description, 'Grade ', -1), ' ', 1), unsigned integer) 
    where r.description like '% Grade%' 
      and CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(
                   r.description, 'Grade ', -1), ' ' ,1),UNSIGNED) > 0;

Query OK, 0 rows affected, 7 warnings (0.02 sec)
Rows matched: 1333  Changed: 0  Warnings: 7

mysql> show warnings;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: ''            |
| Warning | 1292 | Truncated incorrect INTEGER value: ''            |
| Warning | 1292 | Truncated incorrect INTEGER value: ''            |
| Warning | 1292 | Truncated incorrect INTEGER value: 'MyCo'        |
| Warning | 1292 | Truncated incorrect INTEGER value: 'MyCo'        |
| Warning | 1292 | Truncated incorrect INTEGER value: 'MyCo'        |
| Warning | 1292 | Truncated incorrect INTEGER value: 'MyCo'        |
+---------+------+--------------------------------------------------+

What I do not understand is how I can rewrite my query differently given that none of the values being updated match what the warnings are complaining about. BElow is my query where I list the distinct values that are being updated ...

我不明白的是我如何以不同的方式重写我的查询,因为没有更新的值与警告的抱怨相符。 BElow是我的查询,我列出了正在更新的不同值...

mysql> select distinct substring_index(substring_index(
              r.description, 'Grade ', -1), ' ', 1)
          from resource r
          where r.description like '% Grade%'
            and CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(
              r.description, 'Grade ',-1),' ',1),UNSIGNED) > 0;
+-----------------------------------------------------------------------+
| substring_index(substring_index(r.description, 'Grade ', -1), ' ', 1) |
+-----------------------------------------------------------------------+
| 7                                                                     |
| 8                                                                     |
| 11                                                                    |
| 9                                                                     |
| 12                                                                    |
| 10                                                                    |
| 6                                                                     |
+-----------------------------------------------------------------------+

How do I rewrite my update statement so that it updates the same values without tryihng to truncate incorrect integers?

如何重写我的更新语句,以便它更新相同的值而不用tryihng来截断不正确的整数?

5 个解决方案

#1

As far as my knowledge concern you get the warning due to WHERE clause condition

据我所知,你会因WHERE子句条件而得到警告

CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(r.description, 'Grade ',-1),' ',1),UNSIGNED) > 0

As clearly mention in warning statement Truncated due to incorrect INTEGER value for '' and MyCo value.

正如警告声明中明确提到的那样,由于''和MyCo值的INTEGER值不正确而截断。

| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: ''            |
| Warning | 1292 | Truncated incorrect INTEGER value: ''            |
| Warning | 1292 | Truncated incorrect INTEGER value: ''            |
| Warning | 1292 | Truncated incorrect INTEGER value: 'MyCo'        |
| Warning | 1292 | Truncated incorrect INTEGER value: 'MyCo'        |
| Warning | 1292 | Truncated incorrect INTEGER value: 'MyCo'        |
| Warning | 1292 | Truncated incorrect INTEGER value: 'MyCo'

Which indicate that you did not have any specific pattern for value in description column of resource table.

这表示您在资源表的描述列中没有任何特定的值模式。

Like below

description
Foo Grade 100 Bar
Foo Grade 99 Grade 
Foo Grade 98 Grade MyCO

As shown in above code in second & third row you have multiple Grade string in record. Which indirectly affect SUBSTRING_INDEX(SUBSTRING_INDEX(r.description, 'Grade ',-1),' ',1), line. So what my suggestion is please make sure that all record inserted properly. If all record are inserted properly and Check that they contains multiple types of pattern for descripton column.If contains multiple pattern then you have to rewrite this query using UNION. Single query for one pattern and another query for another pattern.

如上面第二行和第三行中的代码所示,您在记录中有多个成绩字符串。这间接影响SUBSTRING_INDEX(SUBSTRING_INDEX(r.description,'Grade', - 1),'',1),line。所以我的建议是请确保所有记录都正确插入。如果所有记录都正确插入并检查它们是否包含描述列的多种类型的模式。如果包含多个模式,则必须使用UNION重写此查询。单个查询一个模式,另一个查询另一个模式。

And just try below updated query.

并尝试下面更新的查询。

   update resource r 
    set grade_id = convert(substring_index(substring_index(
                   r.description, 'Grade ', -1), ' ', 1), unsigned integer) 
    where r.description like '% Grade%' 
    and substring_index(substring_index(r.description, 'Grade ', -1), ' ', 1) REGEXP '[0-9]+';

Hope this explanation helps you.

希望这个解释可以帮到你。

#2

You get the warnings because of the WHERE clause, not the SELECT clause. Since invalid (non numeric) values are converted to 0 the condition

由于WHERE子句而不是SELECT子句,您会收到警告。由于无效(非数字)值被转换为0条件

CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(
          r.description, 'Grade ',-1),' ',1),UNSIGNED) > 0

retuns false. Thus these rows do not appear in the result. Remove that condition in you select statement to see which rows cause the warnings:

回报错误。因此,这些行不会出现在结果中。在select语句中删除该条件以查看哪些行导致警告:

select distinct 
    substring_index(substring_index(
        r.description, 'Grade ', -1), ' ', 1),
    CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(
        r.description, 'Grade ',-1),' ',1),UNSIGNED),
        r.description
from resource r
where r.description like '% Grade%';

Example: http://rextester.com/TVHN10678

Since i don't know your data, i can't help to fix it.

由于我不知道你的数据,我无法解决它。

#3

can you use trim() before convert to remove any whitespaces that may have crept in? those are also the reason for this warning

你可以在转换之前使用trim()去除任何可能已经悄悄进入的空格吗?这些也是这个警告的原因

#4

You can try to use regular expression, something like:

您可以尝试使用正则表达式,例如:

where r.description like '% Grade%' and SUBSTRING_INDEX(SUBSTRING_INDEX( r.description, 'Grade ', -1), ' ' ,1) REGEXP '^[0-9]+$';

其中r.description如'%Grade%'和SUBSTRING_INDEX(SUBSTRING_INDEX(r.description,'Grade',-1),'',1)REGEXP'^ [0-9] + $';

Alternatively if you are not afraid of stepping into gray area: you can also try the same query without CONVERT at all - surprisingly it may just work:

或者,如果你不害怕踩到灰色区域:你也可以在没有CONVERT的情况下尝试相同的查询 - 令人惊讶的是它可能正常工作:

where r.description like '% Grade%' and SUBSTRING_INDEX(SUBSTRING_INDEX( r.description, 'Grade ', -1), ' ' ,1) > 0;

其中r.description如'%Grade%'和SUBSTRING_INDEX(SUBSTRING_INDEX(r.description,'Grade', - 1),'',1)> 0;

(again I am not sure if this is documented behavior, thus - 'gray area').

(我不确定这是否是记录在案的行为,因此 - '灰色区域')。

#5

Somewhere in your resource.description column you have either '' or MyCo as per your warnings output. There is non-numeric data being passed to the convert function, which is where the warnings are coming from.

在您的resource.description列中的某个位置,根据您的警告输出,您有''或MyCo。将非数字数据传递给convert函数,这是警告的来源。

I don't know what your raw data looks like, but if you run the below query, it should identify any rows where resource.description is not in the correct format.

我不知道您的原始数据是什么样的,但如果您运行以下查询,它应该标识resource.description的格式不正确的任何行。

select distinct substring_index(substring_index(r.description, 'Grade ', -1), ' ', 1)
from resource r 
order by r.description

You can try running an EXPLAIN on your query as well. This may give you more insight. I don't know the internals of MySQL, but perhaps the convert function is somehow looking at all rows, despite having a where clause.

您也可以尝试在查询上运行EXPLAIN。这可能会给你更多的见解。我不知道MySQL的内部,但也许转换函数以某种方式查看所有行,尽管有一个where子句。

#1