飛鴿傳書【轉(zhuǎn)】用MYSQL都可能會(huì)遇到的問題：MYSQL字符數(shù)字轉(zhuǎn)換

1.

將字符的數(shù)字轉(zhuǎn)成數(shù)字，比如'0'轉(zhuǎn)成0可以直接用加法來實(shí)現(xiàn)

例如：將pony表中的d 進(jìn)行排序，可d的定義為varchar，可以這樣解決

select * from pony order by (d+0)

2.

在進(jìn)行ifnull處理時(shí)，比如 ifnull(a/b,'0') 這樣就會(huì)導(dǎo)致 a/b成了字符串，因此需要把'0'改成0，即可解決此困擾

3比較數(shù)字和varchar時(shí)，比如a=11，b="11ddddd";

則 select 11="11ddddd"相等

若絕對比較可以這樣：

select binary 11 =binary "11ddddd"

?=======================================附錄1======================================

字符集轉(zhuǎn)換 :?? CONVERT(xxx? USING?? gb2312)

類型轉(zhuǎn)換和SQL Server一樣,就是類型參數(shù)有點(diǎn)點(diǎn)不同? : CAST(xxx? AS?? 類型)? ,?? CONVERT(xxx,類型)，類型必須用下列的類型：
?
? 可用的類型　??
? 二進(jìn)制,同帶binary前綴的效果 : BINARY???
? 字符型,可帶參數(shù) : CHAR()????
? 日期 : DATE????
? 時(shí)間: TIME????
? 日期時(shí)間型 : DATETIME????
? 浮點(diǎn)數(shù) : DECIMAL?????
? 整數(shù) : SIGNED????
? 無符號整數(shù) : UNSIGNED?
?

=======================================附錄2===============================================

http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

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. www.freeeim.com

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.

Previous / Next / Up / Table of Contents

總結(jié)

以上是生活随笔為你收集整理的【转】用MYSQL都可能会遇到的问题：MYSQL字符数字转换的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。