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,類型)，類型必須用下列的類型：   可用的類型　  
  二進位,同帶binary首碼的效果 : BINARY   
  字元型,可帶參數 : CHAR()    
  日期 : DATE    
  時間: TIME    
  日期時間型 : DATETIME    
  浮點數 : DECIMAL     
  整數 : SIGNED    
  不帶正負號的整數 : 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.

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