mysql如何進行類型轉換

這篇文章主要介紹“mysql如何進行類型轉換”的相關知識，小編通過實際案例向大家展示操作過程，操作方法簡單快捷，實用性強，希望這篇“mysql如何進行類型轉換”文章能幫助大家解決問題。

轉換方法：1、用“+”運算符，語法“SELECT 1+'字符串';”；2、用CAST()函數，可將任意類型轉為指定類型，語法“CAST(expr AS type)”；3、用DATE_FORMAT()函數，可將日期按照給定的模式轉換成字符串。

1、隱式類型轉換和顯式類型轉換的概念

隱式類型裝換:

兩個值進行運算或者比較，首先要求數據類型必須一致。如果發現兩個數據類型不一致時就會發生隱式類型轉換。例如，把字符串轉成數字，或者相反：

SELECT 1+'1'; – 字符串1轉成數字
SELECT concat(2,'test'); – 數字2轉成字符串

顯式類型轉換:

利用函數進行數據類型的轉換

2.Cast函數

CAST(expr AS type)

將任意類型的表達式expr轉換成指定類型type的值。type可以是以下任意類型之一：

• BINARY[(N)] ：二進制字符串，轉換后長度小于N個字節（ 不足長度N則尾部補上0x00 ）

• CHAR[(N)] ：字符串，轉換后長度小于N個字符

• DATE ：日期

• DATETIME ：日期時間

• DECIMAL[(M[,N])] ：浮點數，M為數字總位數(包括整數部分和小數部分)，N為小數點后的位數

• SIGNED [INTEGER] ：有符號整數

• TIME ：時間

• UNSIGNED [INTEGER] ：無符號整數

字符串轉成數字

轉成decimal時，從頭掃描字符串直到第一個不為數字的字符為至。對截斷的那一位進行四舍五入

1）默認不限定M,N，轉換為整數，按照小數點后第一位進行四舍五入：

SELECT cast(‘28.82abc’ AS DECIMAL);

2）限定M不限定N，轉換為整數（M盡量大一些）：

SELECT cast(‘28.82abc’ AS DECIMAL(4));

如果M的長度小于實際的數字位數時，會轉換成設定位數的最大值。下面這個轉換中M為1，即顯示不了29這個兩位數，就會顯示1位數中最大的9

SELECT cast(‘28.82abc’ AS DECIMAL(1));

M、N都限定，必須先滿足小數點后的N位小數。下例中，先滿足了1位小數，再取1位整數：

SELECT cast(‘28.82abc’ AS DECIMAL(2,1));

先滿足2位小數，不取整數部分：

SELECT cast(‘28.82abc’ AS DECIMAL(2,2));

SELECT cast(‘28.82abc’ AS UNSIGNED);

SELECT cast(’-28abc’ AS SIGNED);

字符串到日期

SELECT cast(‘2007-12-25’ AS DATETIME);

SELECT cast(‘2007-12-25’ AS DATE);

SELECT cast(‘20:20:20’ AS TIME);

日期格式必須是‘YYYY-MM-DD’

SELECT cast(‘25-12-2007’ AS DATETIME);
SELECT cast(‘2007-may-25’ AS DATETIME);

數字到字符串

SELECT cast(123 AS CHAR);
 SELECT cast(123 AS BINARY);

日期到字符串

SELECT cast(now() AS CHAR);

字符串到二進制字符串（binary string）

二進制字符串：二進制字符串被視為一個連續的字節序列，與字符集無關。非二進制字符串（即我們通常所說的字符串）被視為一個連續排列的字符序列，與字符集有關。所謂與字符集無關，是指與MySQL自己的字符集無關，而是按照操作系統的字符集把字符串轉換成字節進行存儲

兩種字符串的比較方式：二進制字符串的比較方式是一個字節一個字節進行的，比較的依據是兩個字節的二進制值。因為同一個字母的大小寫的數值編碼是不一樣的，因此它是區分大小寫的。另外，由于它和字符集無關，因此也就沒有大寫和小寫字母一說

非二進制字符串的比較方式是一個字符一個字符進行的，比較的依據是兩個字符在字符集中的先后順序。根據使用的校對不同，可以進行區分大小寫的比較和不區分大小寫的比較

使用CAST(str AS BINARY)將字符串轉換成二進制字符串時，通常使用它的快捷方式寫法： BINARY str

查看當前字符集和校對規則設置

SHOW VARIABLES LIKE ‘collation_%’;

在比較表達式中，binary影響后面所有的字符串，并且不會忽略字符串的尾部空格

SELECT ‘a’ = ‘A’ ,BINARY ‘a’ = ‘A’;
 SELECT ‘a’ = 'a ',BINARY ‘a’ = 'a ';

Cast(col_name as date_type)

將指定的列或者表達式轉換為指定的數據類型

使用場景:當兩個值進行比較,但是數據源的表中列的數據不一致,這個時候可以使用cast函數進行轉化

3.Convert函數

CONVERT(expr, type), CONVERT(expr USING sharset_name)

convert函數的作用和cast函數幾乎相同，但是它可以把字符串從一種字符集轉換成另一種字符集。下例將字符串’abc’從默認的字符集轉換成utf8字符集

SELECT convert(‘abc’ USING utf8);

SELECT convert(‘你好’ USING gbk);

如果目標字符集不能表示該字符，則返回亂碼

SELECT convert(‘你好’ USING latin1);

日期字符串轉換函數

DATE_FORMAT(date, format)

將日期date按照給定的模式format轉換成字符串。format中可使用以下模式元素

SELECT date_format(‘2009-10-04 22:23:00’, ‘%W %M %Y’);

SELECT date_format(now(), ‘%W %M %Y’);

SELECT date_format(‘2007-10-04 22:23:00’, ‘%H:%i:%s’);

TIME_FORMAT(date, format)

format中只可使用時、分、秒和微秒模式元素

SELECT time_format(‘22:23:01’, ‘%H.%i.%s’);

STR_TO_DATE(str, format)

將字符串str以指定的模式format轉換成日期。format中可以包含模式元素和字面量，字面量必須匹配str中的字面量：

SELECT str_to_date(‘01,5,2013’,’%d,%m,%Y’);
SELECT str_to_date(‘May 1, 2013’,’%M %d,%Y’);

以下在會話變量@@sql_mode設置中包含no_zero_date和no_zero_in_date時轉換失敗，沒有包含這些設置時轉換成功

SELECT str_to_date(‘a09:30:17’,‘a%h:%i:%s’);
 SELECT str_to_date(‘09:30:17a’,’%h:%i:%s’);

關于“mysql如何進行類型轉換”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識，可以關注億速云行業資訊頻道，小編每天都會為大家更新不同的知識點。