MySQL update column value using CAST-CodePudding

I have simple table (with wrong formatted data):

 ======== 
| weight |
 ======== 
| 100г   |
 -------- 
| 200 г  |
 -------- 
| 300гр  |
 -------- 
| 400 гр |
 -------- 
| 500г.  |
 --------

I can apply format to data when I do select:

SELECT CONCAT((weight   0), ' г') formatted FROM t;

 =========== 
| formatted |
 =========== 
| 100 г     |
 ----------- 
| 200 г     |
 ----------- 
| 300 г     |
 ----------- 
| 400 г     |
 ----------- 
| 500 г     |
 -----------

Now I try to update data using above format:

UPDATE t SET weight = CONCAT((weight   0), ' г');

But got the error:

Truncated incorrect DOUBLE value: '200 г'

Here the fiddle

CodePudding user response：

The same warnings occur on your SELECT:

mysql> SELECT CONCAT((weight   0), ' г') formatted FROM t;
 ----------- 
| formatted |
 ----------- 
| 100 г     |
| 200 г     |
| 300 г     |
| 400 г     |
| 500 г     |
 ----------- 
5 rows in set, 5 warnings (0.00 sec)

mysql> show warnings;
 --------- ------ -------------------------------------------- 
| Level   | Code | Message                                    |
 --------- ------ -------------------------------------------- 
| Warning | 1292 | Truncated incorrect DOUBLE value: '100 г'  |
| Warning | 1292 | Truncated incorrect DOUBLE value: '200 г'  |
| Warning | 1292 | Truncated incorrect DOUBLE value: '300 г'  |
| Warning | 1292 | Truncated incorrect DOUBLE value: '400 г'  |
| Warning | 1292 | Truncated incorrect DOUBLE value: '500 г'  |
 --------- ------ --------------------------------------------

The documentation on CAST() explains this:

A string value containing non-numeric characters which must be truncated prior to conversion raises a warning, as shown here: ...

The warnings aren't fatal if you're just doing SELECT to return a result set, however the documentation on SQL modes explains:

Strict SQL mode applies to the following statements under conditions for which some value might be out of range or an invalid row is inserted into or deleted from a table:

UPDATE is one of those statements. An out of range value only results in a warning if you SELECT it, but in strict mode, you can't use such a dodgy value when inserting or updating data. The strict mode is meant to protect you from accidentally saving truncated data.

So how to solve your problem?

One way is to temporarily disable strict mode in your current session.

SET @sql_mode=@@sql_mode;
SET sql_mode='';
...do your UPDATE...
SET sql_mode=@sql_mode;

Another solution is to use the integer value of your strings more explicitly, without relying on the implicit CAST.

Example:

SELECT CONCAT(REGEXP_SUBSTR(weight, '^[[:digit:]]*'), ' г') formatted FROM t;

 ----------- 
| formatted |
 ----------- 
| 100 г     |
| 200 г     |
| 300 г     |
| 400 г     |
| 500 г     |
 ----------- 
5 rows in set (0.01 sec)

(notice no warnings)

The REGEXP_SUBSTR() function is supported in MySQL 8.0. If you use an older version of MySQL, you'd have to do something with SUBSTR() or else disable the strict sql_mode as I showed.