|
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,类型)，类型必须用下列的类型： 可用的类型 二进制,同带binary前缀的效果 : BINARY 字符型,可带参数 : CHAR() 日期 : DATE 时间: TIME 日期时间型 : DATETIME 浮点数 : DECIMAL 整数 : SIGNED 无符号整数 : UNSIGNED 附录2 http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html mysql转换函数之cast 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> DE>SELECT 1+'1';DE> -> 2 If you use a number in string context, the number automatically is converted to a DE>BINARYDE> string. mysql> DE>SELECT CONCAT('hello you ',2);DE> -> 'hello you 2' MySQL supports arithmetic with both signed and unsigned 64-bit values. If you are using numeric operators (such as DE>+DE> or DE>-DE>) and one of the operands is an unsigned integer, the result is unsigned. You can override this by using the DE>SIGNEDDE> and DE>UNSIGNEDDE> cast operators to cast the operation to a signed or unsigned 64-bit integer, respectively. mysql> DE>SELECT CAST(1-2 AS UNSIGNED)DE> -> 18446744073709551615 mysql> DE>SELECT CAST(CAST(1-2 AS UNSIGNED) AS SIGNED);DE> -> -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, DE>DECIMALDE> column values are regarded as floating-point values.) mysql> DE>SELECT CAST(1 AS UNSIGNED) - 2.0;DE> -> -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, DE>CONVERT()DE> and DE>CAST()DE> return DE>NULLDE> when the DE>NO_ZERO_DATEDE> SQL mode is enabled. As of MySQL 5.0.4, they also produce a warning. (责任编辑：IT) |