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 。