字符串转数字
正常的字符串数字直接转
mysql> select '123'+123;
+-----------+
| '123'+123 |
+-----------+
| 246 |
+-----------+
1 row in set (0.00 sec)
字符串中含有字母
字母在开头
mysql> select 'aa123'+123;
+-------------+
| 'aa123'+123 |
+-------------+
| 123 |
+-------------+
1 row in set, 1 warning (0.00 sec)
字母在数字中间
mysql> select '123aa12'+100;
+---------------+
| '123aa12'+100 |
+---------------+
| 223 |
+---------------+
1 row in set, 1 warning (0.00 sec)
字母在末尾
select '123bb'+100;
+-------------+
| '123bb'+100 |
+-------------+
| 223 |
+-------------+
1 row in set, 1 warning (0.00 sec)
总结
数字英文混杂的字符串进行计算,只能识别到字符串中开头的数字,一旦遇到不是数字的类型就舍弃之后所有的部分。
select "aa" = 0 -------------true;
select "1aa"=0--------------false;
去除小数点后多余的0
select 0+cast(字段 as 字符类型);
mysql> select '3.330000000'+0;
+-----------------+
| '3.330000000'+0 |
+-----------------+
| 3.33 |
+-----------------+
1 row in set (0.00 sec)