標籤: MySQL

MySQL 上可用 cast() 做資料型態轉換，不過 data type 的 reserve word 和一般 CREATE TABLE 時用的不太一樣。詳細參考 MySQL cast funtion 的說明。

轉數字：

• SIGNED、UNSIGNED：相當於 `BIGINT`
• FLOAT(N)、DOUBLE、REAL：浮點數，FLOAT 要指定精確度，用 DOUBLE 比較快
• DECIMAL[M, D]：要指定資料長度

轉字串：

• CHAR[N]、NCHAR[N]：要指定字串長度
• TEXT、LONGTEXT：不支援，只能使用前一項的 reserve word

若資料不是要在 DBMS runtime 處理，而是要輸出以後由其他程式語言處理的話，是可以考慮直接轉 JSON 型態。但若要在 DBMS 裡面使用的話，還是乖乖轉支援的資料型態比較不會出錯。

發現如果 MySQL 中的變數未指定資料型態，會出現一些很雷的行為。

這邊用 JSON 型態：

select json_array(
   100,
   JSON_ARRAY(1, 2)
);
# 輸出：  [100, [1, 2]]

先除存到 @list 以後在讀取，資料型態就會亂掉：

SET @list = json_array(1, 2);
select json_array(
   100,
   @list
);
# 輸出：  [100, "[1, 2]"]

若需要用到變數，建議先宣告變數型態：

DECLARE list JSON;
SET list = json_array(1, 2);

用以下的 SQL 來建立新的 function：

CREATE FUNCTION myfunc(IN str TEXT) RETURNS TEXT RETURN '123';

執行以後會出現錯誤訊息：

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IN str TEXT) returns TEXT return '123'' at line 1

將 function parameter 中的 IN 刪除以後就不會再出現錯誤訊息。

問題是按照 MySQL 官方文件中 create function statements 中的說明，IN 參數是 optional 的，可加可不加，但實際執行時加上 IN 就會出現上面的錯誤。

所以問題來了，是 MySQL 沒有按照文件中的規範實作？還是文件並沒有跟著 MySQL 的實作更新？

使用變數的方式主要有二種，一是使用在變數名稱地一個字元加上 @ ，二是使用 DECLARE 宣告再 SET。

要注意的是 @ 開頭的變數 scope 為整個 session，也就是在不同 function / store procedure 都可以讀取數值、設定數值。如以下範例：

DELIMITER ;;
CREATE FUNCTION hello() RETURNS text
BEGIN
    SET @str = 'hello';
    RETURN @str;
END ;;
DELIMITER ;

DELIMITER ;;
CREATE FUNCTION world() RETURNS text
BEGIN
    SET @str = 'world';
    SET @str = CONCAT(hello(), ' ', @str);
    RETURN @str;
END ;;
DELIMITER ;

SELECT world() as result;

因為會讀、寫到同一個變數，所以輸出會是 hello hello。

若使用 DECLARE 宣告變數，則開變數的 scope 僅在 loop、function、store procedure 中才有效。相同的範例，改使用 DECLARE ：

DELIMITER ;;
CREATE FUNCTION hello() RETURNS TEXT
BEGIN
    DECLARE str TEXT;
    SET str = 'hello';
    RETURN str;
END ;;
DELIMITER ;

DELIMITER ;;
CREATE FUNCTION world() RETURNS TEXT
BEGIN
    DECLARE str text;
    SET str = 'world';
    SET str = CONCAT(hello(), ' ', str);
    RETURN str;
END ;;
DELIMITER ;

SELECT world() AS result;

輸出結果為 hello world。

MySQL 有個叫做 soundex() 的函式，這個函式會回傳字串發音的 fingerprint，藉由這個 fingerprint 來搜尋其他發音類似的字串。

先隨意挑幾個單字：

select soundex("depoart"), soundex("department")
+--------------------+-----------------------+
| soundex("depoart") | soundex("department") |
+--------------------+-----------------------+
| D163               | D16353                |
+--------------------+-----------------------+

depoart 和 department 在前半部有相同的發音，所以 soundex() 回傳的 prefix 是相同的。

如果換成一組完全不同的文字：

select soundex("space"), soundex("system")
+------------------+-------------------+
| soundex("space") | soundex("system") |
+------------------+-------------------+
| S120             | S350              |
+------------------+-------------------+

因為都是 s 開頭，所以回傳值都是 S，但後半部的值就不同了。

這個在查詢類似拼字錯誤上應該蠻有用的，像是：

select case 
    when  soundex("john") = soundex("jone")
    then 1 else 0
end
as familiar
+----------+
| familiar |
+----------+
| 1        |
+----------+