MySQL 轉換函式與運算子

標籤：mysql   字串   轉換   函數   

Table 12.14 轉換函式(Cast Function)

名稱	說明
BINARY	將 string 轉換為二進位 string
CAST()	將某個值轉換為特定類型
CONVERT()	將某個值轉換為特定類型

• BINARY

BINARY 運算子將緊隨其後的 string 轉換為 二進位字串。主要用來強制進行按位元組進行比較(byte by byte),位元組而不是字元的字元。這使得字串比較是區分大小寫, 不管原始的列定義是否是 BINARY 或者 BLOB。BINARY 也對字串末尾的空格敏感。

SELECT ‘a‘ = ‘A‘;

1

SELECT BINARY ‘a‘ = ‘A‘;

0

SELECT ‘a‘ = ‘a    ‘;

1

SELECT BINARY ‘a‘ = ‘a   ‘;

0

在上面的比較中, BINARY 影響的是整個比較操作; 不管哪個運算元放在前面, 結果都是一樣的。

還有一種情況，BINARY 不對等號起作用：

 SELECT ‘a‘ = BINARY ‘a    ‘;

1

BINARY str 其實是 CAST(str AS BINARY) 的縮寫。

有時候, 如果將索引列轉換為 BINARY, MySQL可能不會使用索引。

• CAST(expr AS type)

CAST() 函數接收任意類型的運算式, 並根據指定類型返回相應的結果值, 跟 CONVERT() 很相似, 除了使用的文法形式上有一點區別, 所以請參考下面的 CONVERT() 函數。

• CONVERT(expr,type), CONVERT(expr USING transcoding_name)

CONVERT()和 CAST() 函數都是接收任意類型的運算式, 並根據指定類型返回相應的結果值。

CAST() 和 CONVERT(... USING ...) 都是標準的SQL文法。而沒有 USING 的 CONVERT() 是 ODBC 的文法。

USING 方式的 CONVERT() 在不同的字元集之間進行資料轉換。在MySQL中, 轉碼的名稱和相應的字元集名稱一致。例如, 下面的語句將字串 ‘abc’ 從預設字元集轉換為 utf8 字元集:

SELECT CONVERT(‘abc‘ USING utf8);

轉換函式的結果可以是以下這些類型:

• BINARY[(N)]

• CHAR[(N)]

• DATE

• DATETIME

• DECIMAL[(M[,D])]

• SIGNED [INTEGER]

• TIME

• UNSIGNED [INTEGER]

BINARY 產生的是二進位形式的 string 資料類型。更多細節請參考 11.4.2節, “The BINARY and VARBINARY Types” 。如果傳入了選擇性參數N, 那麼 BINARY(N) 的轉換結果最多為 N 個位元組。如果結果小於N個位元組,則用 0x00 來填充。

CHAR(N)的結果為最多N個字元。

一般來說,用不區分大小寫方式並不能比較 BLOB 值或者其他二進位串, 因為二進位串是沒有字元集的,因此也沒有字母的概念。如果要不區分大小寫, 可以用 CONVERT() 將值轉換為非二進位的字串再來比較。比較的結果根據字元集排序而定。例如,假設字元集不區分大小寫, 那麼 like 操作也就不區分大小寫:

SELECT ‘A‘ LIKE CONVERT(blob_col USING latin1) FROM tbl_name;

要使用其他字元集, 只要把裡面的 latin1 替換掉就行。為轉換後的字串指定特定的定序, 可以在 CONVERT() 函數調用後面跟上 COLLATE 從句, 正如 10.1.9.2 節 “CONVERT() and CAST()” 中所描述的. 例如,使用 latin1_german1_ci 排序:

SELECT ‘A‘ LIKE CONVERT(blob_col USING latin1) COLLATE latin1_german1_ci  FROM tbl_name;

CONVERT() 可以在不同的字元集之中進行比較。

LOWER() 和 UPPER() 對於二進位字串是無效的(包括 BINARY, VARBINARY, BLOB)。要進行大小寫轉換,需要先將字串轉換成非二進位形式:

mysql> SET @str = BINARY ‘New York‘;mysql> SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));+-------------+-----------------------------------+| LOWER(@str) | LOWER(CONVERT(@str USING latin1)) |+-------------+-----------------------------------+| New York    | new york                          |+-------------+-----------------------------------+

轉換函式可以用來建立特定類型的列,比如在 CREATE TABLE ... SELECT語句之中:

CREATE TABLE new_table SELECT CAST(‘2000-01-01‘ AS DATE);

轉換函式也可以用來按定義的單詞將 ENUM 列排序 。正常情況下, 枚舉列是根據內部的數值表示來進行排序的。按字母排序 CHAR 類型的結果:

SELECT enum_col FROM tbl_name ORDER BY CAST(enum_col AS CHAR);

CAST(str AS BINARY) 和 BINARY str 等價。CAST(expr AS CHAR) 將運算式當作預設字元集來處理。

CAST() 可能會改變複雜運算式的結果，例如 CONCAT(‘Date: ‘,CAST(NOW() AS DATE))。

這裡就不應該使用 CAST() 來提取不同格式的資料,而應該使用字串函數，如 LEFT() 或者 EXTRACT()。詳情請參考 Section 12.7, “Date and Time Functions”。

要把字串轉換為數值來進行處理, 一般是不需要手工處理的，MySQL會進行隱式的類型轉換:

SELECT 1+‘1‘;

2

在算術運算中, string 會在運算式求值階段轉換為浮點數。

如果需要將數字當成字串來處理, MySQL也會自動進行轉換:

SELECT CONCAT(‘hello you ‘,2);

‘hello you 2’

在 MySQL 5.6.4之前的版本,用 CAST() 處理 TIMESTAMP 時， 如果不從具體的表中選取值, MySQL 5.6 會在執行轉換之前把值優先當成字串來對待。這在轉換為數字時可能會導致截斷,如下所示:

mysql> SELECT CAST(TIMESTAMP ‘2014-09-08 18:07:54‘ AS SIGNED);+-------------------------------------------------+| CAST(TIMESTAMP ‘2014-09-08 18:07:54‘ AS SIGNED) |+-------------------------------------------------+|                                            2014 |+-------------------------------------------------+1 row in set, 1 warning (0.00 sec)mysql> SHOW WARNINGS;+---------+------+----------------------------------------------------------+| Level   | Code | Message                                                  |+---------+------+----------------------------------------------------------+| Warning | 1292 | Truncated incorrect INTEGER value: ‘2014-09-08 18:07:54‘ |+---------+------+----------------------------------------------------------+1 row in set (0.00 sec)

但如果從一張表中選取行時並不會這樣,如下所示:

USE test;

Database changed

CREATE TABLE c_test (col TIMESTAMP);

Query OK, 0 rows affected (0.07 sec)

INSERT INTO c_test VALUES (‘2014-09-08 18:07:54‘);

Query OK, 1 row affected (0.05 sec)

SELECT col, CAST(col AS UNSIGNED) AS c_col FROM c_test;

>
+———————+—————-+
| col | c_col |
+———————+—————-+
| 2014-09-08 18:07:54 | 20140908180754 |
+———————+—————-+
1 row in set (0.00 sec)

在MySQL 5.6.4 之後, 修複了這個問題,如下所示:

SELECT CAST(TIMESTAMP ‘2014-09-08 18:07:54‘ AS SIGNED);

>
+————————————————-+
| CAST(TIMESTAMP ‘2014-09-08 18:05:07’ AS SIGNED) |
+————————————————-+
| 20140908180754 |
+————————————————-+
1 row in set (0.00 sec)

關於數字和字串的隱式轉換, 參見 12.2節 “Type Conversion in Expression Evaluation”.

MySQL支援有符號的和無符號的64位算術運算。如果您使用的是數字運算子(如加 + 或減 -), 其中的一個運算元是不帶正負號的整數, 那預設情況下結果就是無符號數(參見 12.6.1 算術運算子)。可以通過指定 SIGNED 或者 UNSIGNED 來進行轉換。

SELECT CAST(1-2 AS UNSIGNED)

18446744073709551615

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

-1

如果有運算元是浮點值, 那麼結果就是浮點值, 不受前面規則的影響。(在這種情況下, DECIMAL 列被視為浮點值。)

SELECT CAST(1 AS UNSIGNED) - 2.0;

-1.0

SQL模式影響轉換操作的結果。例如:

• 如果轉換零值的日期串為日期, CONVERT() 和 CAST() 都會返回 NULL , 並在 NO_ZERO_DATE 模式下產生警告。

• 對於整數的減法,如果啟用了 NO_UNSIGNED_SUBTRACTION 模式, 減法結果是有符號數,即便其中一個是無符號數。

更多資訊請參見 5.1.7節 “Server SQL Modes”。

如何將 BLOB 轉換為 UTF8 的 char

首先，請查看 BLOB 裡面儲存的是什麼編碼的byte。是 utf8 還是其他字元集?

CAST(a.ar_options AS CHAR(10000) CHARACTER SET utf8) 

在這裡必須指定正確的字元集, 對應於 BLOB 中儲存的編碼。如果裡面儲存的是 utf8編碼, 那麼就是上面這樣。如果儲存的是 latin1 字元集, 那麼就需要設定為 latin1 。

原文連結: Cast Functions and Operators

翻譯日期: 2015年10月03日

翻譯人員: 鐵錨 http://blog.csdn.net/renfufei

MySQL 轉換函式與運算子