mysql转换字符串为数字_mysql字符与数字转换「建议收藏」

本节内容：

mysql字符与数字转换的方法

1，将字符的数字转成数字，比如’0’转成0可以直接用加法实现。

例如：将pony表中的d 进行排序，可d的定义为varchar：

复制代码 代码示例:

select * from pony order by (d+0)

2，在进行ifnull处理时，比如 ifnull(a/b,’0′) 会导致 a/b成了字符串，因此需要把’0’改成0。

3，比较数字和varchar时，比如a=11，b=”11ddddd”;

则 select 11=”11ddddd”相等

绝对比较可以这样：

复制代码 代码示例:

select binary 11 =binary “11ddddd”

附1，

字符集转换 :   CONVERT(xxx  USING   gb2312)

类型转换和SQL Server一样,就是类型参数有点点不同  : CAST(xxx  AS   类型)  ,   CONVERT(xxx,类型)，类型必须用下列的类型：

1，可用的类型

2，二进制,同带binary前缀的效果 : BINARY

3，字符型,可带参数 : CHAR()

4，日期 : DATE

5，时间: TIME

6，日期时间型 : DATETIME

7，浮点数 : DECIMAL

8，整数 : SIGNED

9，无符号整数 : UNSIGNED

附2，

To cast a string to a numeric value in numeric context, you normally do not have to do anything other than to use the string value as though it were a number:

复制代码 代码示例:

mysql> SELECT 1+’1′;

-> 2

If you use a number in string context, the number automatically is converted to a BINARY string.

复制代码 代码示例:

mysql> SELECT CONCAT(‘hello you ‘,2);

-> ‘hello you 2’

MySQL supports arithmetic with both signed and unsigned 64-bit values. If you are using numeric operators (such as + or -) and one of the operands is an unsigned integer, the result is unsigned. You can override this by using the SIGNED and UNSIGNED cast operators to cast the operation to a signed or unsigned 64-bit integer, respectively.

复制代码 代码示例:

mysql> SELECT CAST(1-2 AS UNSIGNED)

-> 18446744073709551615

mysql> SELECT CAST(CAST(1-2 AS UNSIGNED) AS SIGNED);

-> -1

Note that if either operand is a floating-point value, the result is a floating-point value and is not affected by the preceding rule. (In this context, DECIMAL column values are regarded as floating-point values.)

复制代码 代码示例:

mysql> SELECT CAST(1 AS UNSIGNED) – 2.0;

-> -1.0

If you are using a string in an arithmetic operation, this is converted to a floating-point number.

If you convert a “zero” date string to a date, CONVERT() and CAST() return NULL when the NO_ZERO_DATE SQL mode is enabled. As of MySQL 5.0.4, they also produce a warning.

有关MYSQL字符与数字转换的方法，就介绍这些吧，希望对大家有所帮助。