（大資料工程師學習路徑）第五步 MySQL參考手冊中文版----MySQL資料類型

標籤：

一、文字值1. 字串

字串指用單引號(‘‘’)或雙引號(‘"’)引起來的字元序列。例如：

‘a string‘"another string"

如果SQL伺服器模式啟用了NSI_QUOTES，可以只用單引號引用字串。用雙引號引用的字串被解釋為一個識別符。

字串可以有一個可選字元集引介詞和COLLATE子句：

[_charset_name]‘string‘ [COLLATE collation_name]

例如：

SELECT _latin1‘string‘;SELECT _latin1‘string‘ COLLATE latin1_danish_ci;

在字串中，某些序列具有特殊含義。這些序列均用反斜線(‘\’)開始，即所謂的逸出字元。MySQL識別下面的逸出序列：

這些序列對大小寫敏感。例如，‘\b’解釋為退格，但‘\B’解釋為‘B’。

‘\%’和‘_’序列用於搜尋可能會解釋為萬用字元的模式比對環境中的‘%’和‘’文字執行個體。參見12.3.1節，“字串比較函數”。請注意如果你在其它環境中使用‘\%’或‘\’，它們返回字串‘\%’和‘_’，而不是‘%’和‘_’。

在其它逸出序列中，反斜線被忽略。也就是說，逸出字元解釋為彷彿沒有轉義。 有幾種方式可以在字串中包括引號：

• 在字串內用‘‘’引用的‘‘’可以寫成‘‘‘’。

• 在字串內用‘"’引用的‘"’可以寫成‘""’。

• 可以在引號前加逸出字元(‘\’)。

• 在字串內用‘"’引用的‘‘’不需要特殊處理，不需要用雙字元或轉義。同樣，在字串內用‘‘’引用的‘"’也不需要特殊處理。

下面的SELECT語句顯示了引用和轉義如何工作：

mysql> SELECT ‘hello‘, ‘"hello"‘, ‘""hello""‘, ‘hel‘‘lo‘, ‘\‘hello‘;mysql> SELECT "hello", "‘hello‘", "‘‘hello‘‘", "hel""lo", "\"hello";mysql> SELECT ‘This\nIs\nFour\nLines‘;mysql> SELECT ‘disappearing\ backslash‘;

如果你想要在字串列插入入位元據(例如BLOB)，必須通過逸出序列表示下面的字元：

當編寫應用程式時，在包含這些特殊字元的字串用於發送到MySQL伺服器的SQL語句中的資料值之前，必須對它們正確進行轉義。可以用兩種方法來完成：

• 用轉義特殊字元的函數處理字串。例如，在C程式中，可以使用mysql_real_escape_string() C API函數來逸出字元。

• 顯式轉義特殊字元，許多MySQL API提供了預留位置功能，允許你在查詢字串中插入特殊標記，然後當你發出查詢時將資料值同它們綁定起來。在這種情況下，API關注轉義值中的特殊字元

2. 數值

整數用一系列阿拉伯數字表示。浮點數使用‘.’作為十進位間隔符。兩種類型的數值均可以在前面加一個‘-’來表示負值。

合法整數的例子：

1221 0 -32 合法浮點數的例子：

294.42 -32032.6809e+10 148.00 整數可以用在浮點環境中；它被解釋為與浮點數等效。

3. 十六進位值

MySQL支援十六進位值。在數字上下文中，十六進位數如同整數(64位精度)。在字串上下文，如同二進位字串，每對十六進位數字被轉換為一個字元：

mysql> SELECT x‘4D7953514C‘；    -> ‘MySQL‘mysql> SELECT 0xa+0；    -> 10mysql> SELECT 0x5061756c；    -> ‘Paul‘

十六進位值的預設類型是字串。如果想要確保該值作為數文書處理，可以使用CAST(...AS UNSIGNED)：

mysql> SELECT 0x41，CAST(0x41 AS UNSIGNED)；    -> ‘A‘，65

0x文法基於ODBC。十六進位字串通常用於ODBC以便為BLOB列提供值。x’hexstring’文法基於標準SQL。

可以用HEX()函數將一個字串或數字轉換為十六進位格式的字串：

mysql> SELECT HEX(‘cat‘)；    -> ‘636174‘mysql> SELECT 0x636174；    -> ‘cat‘

4. 布爾值

常量TRUE等於1，常量FALSE等於0。常量名可以寫成大寫或小寫。

mysql> SELECT TRUE，true，FALSE，false；    -> 1，1，0，0

5. 位欄位值

可以使用b‘value‘符號寫位欄位值。value是一個用0和1寫成的二進位值。

位欄位符號可以方便指定分配給BIT列的值：

mysql> CREATE TABLE t (b BIT(8));mysql> INSERT INTO t SET b = b‘11111111‘;mysql> INSERT INTO t SET b = b‘1010‘;

6. NULL值

NULL值表示“沒有資料”。NULL可以寫成大寫或小寫。

請注意NULL值不同於數字類型的0或字串類型的Null 字元串。

對於用LOAD DATA INFILE或SELECT ...INTO OUTFILE執行的文字檔匯入或匯出操作，NULL用序列\N表示。

二、資料庫、表、索引、列和別名

資料庫、表、索引、列和別名是識別符。該節描述了在MySQL中識別符的允許的文法。

下面的表描述了每類識別符的最大長度和允許的字元。

除了表內註明的限制，識別符不可以包含ASCII 0或值為255的位元組。資料庫、表和列名不應以空格結尾。在識別符中可以使用引號識別符，儘管應儘可能避免這樣使用。

識別符用Unicode(UTF8)儲存。在.frm檔案中儲存的表定義的識別符和在mysql資料庫中的授權表儲存的識別符也用Unicode(UTF8)儲存。在MySQL 5.1中授權表(和其它表)的字串列的大小等於字元個數；這說明(不象以前版本的MySQL)你可以在這些列儲存的值中使用多位元組字元而不需要降低字元個數。

識別符可以引起來也可以不引起來。如果識別符是一個保留字或包含特殊字元，無論何時使用，必須將它引起來。

識別符的引用符是反勾號(‘`’)：

mysql> SELECT * FROM `select` WHERE `select`.id > 100;

如果SQL伺服器模式包括ANSI_QUOTES模式選項，還可以用雙引號將識別符引起來：

mysql> CREATE TABLE "test" (col INT);ERROR 1064: You have an error in your SQL syntax. (...)mysql> SET sql_mode=‘ANSI_QUOTES‘;mysql> CREATE TABLE "test" (col INT);Query OK, 0 rows affected (0.00 sec)

如果你引用識別符，可以在識別符內包括識別符引用符。如果識別符內包括的字元與引用識別符的字元相同，則需要用雙字元。下面的語句建立一個名為a`b包含列c"d的表：

mysql> CREATE TABLE `a``b` (`c"d` INT)；

建議不要使用XeX模式的名，例如1e或2e2，因為類似1e+1的運算式比較模糊。根據上下文，它可以解釋為運算式1e + 1或數字1e+1。

使用MD5產生表名時應仔細，因為它可能產生不合法的表名，如上所述。

1. 識別符限制條件

MySQL允許使用由單個識別符或多個識別符組成的名字。多部分名各組件之間應以句點(‘.’)間隔開。多部分名的開頭部分作為限定詞，後面的識別符被解釋。

在MySQL中可以引用下面形式的列：

如果多部分名的組件需要引用，應分別將它們引起來而不要將整個名引起來。例如，my-tables.my-column 有效，而my-tables.my-column無效。

不需要在語句中為列指定tbl_name或db_name.tbl_name首碼，除非列會很模糊。假定表t1和t2各包含一個列c，你使用SELECT語句在t1和t2中搜尋c。在這種情況下，c很模糊，因為它在語句中使用的表內不唯一。你必須用表名t1.c或t2.c限定它，表示指哪個表。同樣，要想用同一語句搜尋資料庫db1中的表t和資料庫db2中的表t，你必須將那些表中的列指為db1.t.col_name和db2.t.col_name。

限定名中句點後面的字必須為一個識別符，因此不需要將它引起來，即使是一個保留字。

文法.tbl_name表示當前資料庫中的tbl_name。該文法與ODBC相容，因為某些ODBC程式在表名前面加首碼‘.’字元。

2. 識別符大小寫敏感性

在MySQL中，資料庫對應資料目錄中的目錄。資料庫中的每個表至少對應資料庫目錄中的一個檔案(也可能是多個，取決於儲存引擎)。因此，所使用作業系統的大小寫敏感性決定了資料庫名和表名的大小寫敏感性。這說明在大多數Unix中資料庫名和表名對大小寫敏感，而在Windows中對大小寫不敏感。一個顯著的例外情況是Mac OS X，它基於Unix但使用預設檔案系統類型(HFS+)，對大小寫不敏感。然而，Mac OS X也支援UFS卷，該卷對大小寫敏感，就像Unix一樣。

注釋：儘管在某些平台中資料庫名和表名對大小寫不敏感，不應在同一查詢中使用不同的大小寫來引用給定的資料庫或表。下面的查詢不會工作，因為它同時引用了表my_tables和as MY_tables：

mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;

列、索引、儲存子程式和觸發器名在任何平台上對大小寫不敏感，列的別名也不敏感。

預設情況，表別名在Unix中對大小寫敏感，但在Windows或Mac OS X中對大小寫不敏感。下面的查詢在Unix中不會工作，因為它同時引用了別名a和A：

mysql> SELECT col_name FROM tbl_name AS a    -> WHERE a.col_name = 1 OR A.col_name = 2;

然而，該查詢在Windows中是可以的。要想避免出現差別，最好採用一致的轉換，例如總是用小寫建立並引用資料庫名和表名。在大多數移植和使用中建議使用該轉換。

在MySQL中如何在硬碟上儲存和使用表名和資料庫名由lower_case_tables_name系統變數確定，可以在啟動mysqld時設定。lower_case_tables_name可以採用下面的任一值：

在Windows和Mac OS X中，lower_case_tables_name的 預設值是1。

如果只在一個平台上使用MySQL，通常不需要更改lower_case_tables_name變數。然而，如果你想要在對大小寫敏感不同的檔案系統的平台之間跳躍表，會遇到困難。例如，在Unix中，my_tables和MY_tables是兩個不同的表，但在Windows中，這兩個表名相同。要想避免由於資料庫或表名的大小寫造成的資料轉移問題，可使用兩個選項：

• 在任何系統中可以使用lower_case_tables_name=1。使用該選項的不利之處是當使用SHOW TABLES或SHOW DATABASES時，看不出名字原來是用大寫還是小寫。

• 在Unix中使用lower_case_tables_name=0，在Windows中使用lower_case_tables_name=2。這樣了可以保留資料庫名和表名的大小寫。不利之處是必須確保在Windows中查詢總是用正確大小寫引用資料庫名和表名。如果將查詢轉移到Unix中，由於在Unix中大小寫很重要，如果大小寫不正確，它們不工作。

例外：如果你正使用InnoDB表，在任何平台上均應將lower_case_tables_name設定為1，以強制將名轉換為小寫。

請注意在Unix中將lower_case_tables_name設定為1之前，重啟mysqld之前，必須先將舊的資料庫名和表名轉換為小寫。

三、使用者變數

可以先在使用者變數中儲存值然後在以後引用它；這樣可以將值從一個語句傳遞到另一個語句。使用者變數與串連有關。也就是說，一個用戶端定義的變數不能被其它用戶端看到或使用。當用戶端退出時，該用戶端串連的所有變數將自動釋放。

使用者變數的形式為@varname，其中變數名var_name可以由當前字元集的文字數字字元、‘.’、‘’和‘$’組成。 預設字元集是cp1252 (Latin1)。可以用mysqld的--default-character-set選項更改字元集。

設定使用者變數的一個途徑是執行SET語句：

SET @var_name = expr [, @var_name = expr] ...

對於SET，可以使用=或:=作為分配符。分配給每個變數的expr可以為整數、實數、字串或者NULL值。

也可以用語句代替SET來為使用者變數分配一個值。在這種情況下，分配符必須為:=而不能用=，因為在非SET語句中=被視為一個比較 操作符：

mysql> SET @t1=0, @t2=0, @t3=0;mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;

使用者變數可以用於運算式中。目前不包括明顯需要文字值的上下文中，例如SELECT語句的LIMIT子句，或者LOAD DATA語句的IGNORE number LINES子句。

如果使用沒有初始化的變數，其值是NULL。

如果使用者變數分配了一個字串值，其字元集和校對規則與該字串的相同。使用者變數的可壓縮性（coercibility）是隱含的。(即為表列值的相同的可壓縮性（coercibility）。

注釋：在SELECT語句中，運算式發送到用戶端後才進行計算。這說明在HAVING、GROUP BY或者ORDER BY子句中，不能使用包含SELECT列表中所設的變數的運算式。例如，下面的語句不能按期望工作：

mysql> SELECT (@aa:=id) AS a，(@aa+3) AS b 從tbl_name HAVING b=5；

HAVING子句中引用了SELECT列表中的運算式的別名，使用@aa。不能按期望工作：@aa不包含當前行的值，而是前面所選的行的id值。

一般原則是不要在語句的一個部分為使用者變數分配一個值而在同一語句的其它部分使用該變數。可能會得到期望的結果，但不能保證。

設定變數並在同一語句中使用它的另一個問題是變數的預設結果的類型取決於語句前面的變數類型。下面的例子說明了該點：

mysql> SET @a=‘test‘;mysql> SELECT @a,(@a:=20) FROM tbl_name;

對於該 SELECT語句，MySQL向用戶端報告第1列是一個字串，並且將@a的所有訪問轉換為字串，即使@a在第2行中設定為一個數字。執行完SELECT語句後，@a被視為下一語句的一個數字。

要想避免這種問題，要麼不在同一個語句中設定並使用相同的變數，要麼在使用前將變數設定為0、0.0或者‘‘以定義其類型。

未分配的變數有一個值NULL，類型為字串

四、系統變數

MySQL可以訪問許多系統和串連變數。當伺服器運行時許多變數可以動態更改。這樣通常允許你修改伺服器操作而不需要停止並重啟伺服器。

mysqld伺服器維護兩種變數。全域變數影響伺服器整體操作。會話變數影響具體用戶端串連的操作。

當伺服器啟動時，它將所有全域變數初始化為預設值。這些預設值可以在選項檔案中或在命令列中指定的選項變更。伺服器啟動後，通過串連伺服器並執行SET GLOBAL var_name語句，可以動態更改這些全域變數。要想更改全域變數，必須具有SUPER許可權。

伺服器還為每個串連的用戶端維護一系列會話變數。在串連時使用相應全域變數的當前值對用戶端的會話變數進行初始化。對於動態會話變數，用戶端可以通過SET SESSION var_name語句更改它們。設定會話變數不需要特殊許可權，但用戶端只能更改自己的會話變數，而不能更改其它用戶端的會話變數。

對於全域變數的更改可以被訪問該全域變數的任何用戶端看見。然而，它隻影響更改後串連的客戶的從該全域變數初始化的相應會話變數。不影響目前已經串連的用戶端的會話變數(即使用戶端執行SET GLOBAL語句也不影響)。

可以使用幾種文法形式來設定或檢索全域或會話變數。下面的例子使用了sort_buffer_sizeas作為樣本變數名。

要想設定一個GLOBAL變數的值，使用下面的文法：

mysql> SET GLOBAL sort_buffer_size=value;mysql> SET @@global.sort_buffer_size=value;

要想設定一個SESSION變數的值，使用下面的文法：

mysql> SET SESSION sort_buffer_size=value;mysql> SET @@session.sort_buffer_size=value;mysql> SET sort_buffer_size=value;

LOCAL是SESSION的同義字。

如果設定變數時不指定GLOBAL、SESSION或者LOCAL，預設使用SESSION。

要想檢索一個GLOBAL變數的值，使用下面的文法：

mysql> SELECT @@global.sort_buffer_size;mysql> SHOW GLOBAL VARIABLES like ‘sort_buffer_size‘;

要想檢索一個SESSION變數的值，使用下面的文法：

mysql> SELECT @@sort_buffer_size;mysql> SELECT @@session.sort_buffer_size;mysql> SHOW SESSION VARIABLES like ‘sort_buffer_size‘;

這裡，LOCAL也是SESSION的同義字。

當你用SELECT @@var_name搜尋一個變數時(也就是說，不指定global.、session.或者local.)，MySQL返回SESSION值（如果存在），否則返回GLOBAL值。

對於SHOW VARIABLES，如果不指定GLOBAL、SESSION或者LOCAL，MySQL返回SESSION值。

當設定GLOBAL變數需要GLOBAL關鍵字但檢索時不需要它們的原因是防止將來出現問題。如果我們移除一個與某個GLOBAL變數具有相同名字的SESSION變數，具有SUPER許可權的客戶可能會意外地更改GLOBAL變數而不是它自己的串連的SESSION變數。如果我們添加一個與某個GLOBAL變數具有相同名字的SESSION變數，想更改GLOBAL變數的客戶可能會發現只有自己的SESSION變數被更改了。

1. 結構式系統變數

結構式變數在兩個方面不同於常規系統變數：

• 其值是一個含組件的結構，可以指定伺服器參數，一般緊密相關。

• 可能是給定類型的結構式變數的幾個執行個體。每個執行個體有一個不同的名，指向伺服器維護的不同的資源。

MySQL 5.1支援結構式變數類型，可以指定監控索引值快取作業的參數。索引值緩衝結構式變數有以下組件：

• key_buffer_size

• key_cache_block_size

• key_cache_division_limit

• key_cache_age_threshold

要引用結構式變數執行個體的組件，可以使用instance_name.component_name格式的複合名。例如：

hot_cache.key_buffer_sizehot_cache.key_cache_block_sizecold_cache.key_cache_block_size

對於每個結構式系統變數，總是預定義名為default的一個執行個體。如果不使用任何執行個體名引用結構式變數的一個組件，default執行個體被使用。這樣，default.key_buffer_size和key_buffer_sizeboth均指向同一系統變數。

結構式變數執行個體和組件的命名規則為：

• 對於給定類型的結構式變數，每個執行個體必須有一個在該類變數中唯一的一個名。但是，在不同類型的結構式變數中執行個體名不需要唯一。例如，每個結構式變數有一個執行個體default，因此在不同的變數類型中default不是唯一的。

• 每個結構式變數類型的組件名在所有系統變數名中必須是唯一的。如果不是這樣(也就是說，如果兩個不同類型的結構式變數能夠共用組件成員名)，將不清楚使用哪個預設結構式變數來用作沒有使用執行個體名限定的成員名。

• 如果結構式變數執行個體名作為未引起來的識別符不合法，將它用反勾號引起來作為識別符。例如，hot-cache不合法，但`hot-cache`合法。

• global、session和local不是合法執行個體名。這樣可以避免與引用非結構式系統變數的符號的衝突，例如@@global.var_name。

目前，這前兩個規則不可能違背，因為唯一的結構式變數類型是索引值緩衝。在將來建立其它類型的結構式變數，這些規則將有重要的意義。

一個例外是，可以在可能出現簡單變數名的上下文中使用複合名引用結構式變數組件。例如，可以使用一個命令列選項未某個結構式變數分配一個值：

shell> mysqld --hot_cache.key_buffer_size=64K

在選項檔案中，使用：

[mysqld]hot_cache.key_buffer_size=64K

如果用該選項啟動伺服器，除了預設大小為8MB的預設索引值緩衝，還建立一個名為hot_cache的索引值緩衝，大小為64KB。

假定你這樣啟動伺服器：

shell> mysqld --key_buffer_size=256K        --extra_cache.key_buffer_size=128K        --extra_cache.key_cache_block_size=2048

在這種情況下，伺服器將預設索引值緩衝的大小設定為256KB。(也可以寫成--default.key_buffer_size=256K）。 並且，伺服器建立一個名為extra_cache的第2個索引值緩衝，大小為128KB，緩衝表索引塊的塊緩衝區的大小設定為2048位元組。

在下面的例子中，用3個不同的索引值緩衝（大小比例為3:1:1）啟動伺服器：

shell> mysqld --key_buffer_size=6M        --hot_cache.key_buffer_size=2M        --cold_cache.key_buffer_size=2M

也可以在運行時設定和檢索結構式變數值。例如，要想將名為hot_cache的一個索引值緩衝的大小設定為10MB，使用下面任何一個語句：

mysql> SET GLOBAL hot_cache.key_buffer_size = 10*1024*1024;mysql> SET @@global.hot_cache.key_buffer_size = 10*1024*1024;

要想檢索緩衝大小，執行：

mysql> SELECT @@global.hot_cache.key_buffer_size;

但是，下面的語句不工作。變數不解釋為一個複合名，而是解釋為LIKE模式比對操作的簡單字串：

mysql> SHOW GLOBAL VARIABLES LIKE ‘hot_cache.key_buffer_size‘;

這是一個在可能出現簡單的變數名時使用結構式變數名的例子。

五、注釋文法

MySQL伺服器支援3種注釋風格：

• 從‘#’字元從行尾。

• 從‘-- ’序列到行尾。請注意‘-- ’(雙破折號)注釋風格要求第2個破折號後面至少跟一個空格符(例如空格、tab、分行符號等等)。該文法與標準SQL注釋文法稍有不同，後者將在1.8.5.7, “‘--’作為注釋起始標記”中討論。

• 從/序列到後面的/序列。結束序列不一定在同一行中，因此該文法允許注釋跨越多行。

下面的例子顯示了3種風格的注釋：

mysql> SELECT 1+1;     # This comment continues to the end of linemysql> SELECT 1+1;     -- This comment continues to the end of linemysql> SELECT 1 /* this is an in-line comment */ + 1;mysql> SELECT 1+/*this is amultiple-line comment*/1;

上述的注釋文法適用於mysqld伺服器如何分析SQL語句。發送到伺服器之前，mysql客戶程式也執行部分語句解析。(例如，它通過解析來確定在多語句行中的語句邊界）。

在MySQL 5.1中，mysql解析/ .../注釋的唯一局限性是結合該風格的注釋定界符使用的歎號標記了有條件執行的SQL語句部分。適用於互動式運行mysql和將命令放入一個檔案中，並以批處理模式使用mysql來處理mysql < file_name的檔案。

六、MySQL中保留字的處理

嘗試使用一個識別符，例如使用嵌入式MySQL資料類型或函數名作為表名或列名，例如TIMESTAMP或GROUP，會造成一個常見問題。允許你這樣操作(例如，ABS可以作為一個列名)。但是，預設情況下，在數調用中在函數名和後面的‘(’字元之間不允許有空格。該要求使函數調用與列名引用不同。

該行為的不利結果是在某些上下文中省略一個空格會使識別符解釋為函數名。例如，該語句合法：

mysql> CREATE TABLE abs (val INT)；

但省略abs後面的空格會造成語法錯誤，因為省略後該語句好像要調用ABS()函數：

mysql> CREATE TABLE abs(val INT);

如果SQL伺服器模式包括IGNORE_SPACE模式值，伺服器允許函數調用時在函數名和後面的‘(’字元之間有空格。這樣使函數名被視為保留字。

限定名中句點後面的字必須為一個識別符，因此不需要將它引起來，即使它是一個保留字。

在MySQL中，下表中的字顯式被保留。其中大多數字進位被標準SQL用作列名和/或表名(例如，GROUP)。少數被保留了，因為MySQL需要它們，(目前)使用yacc解析程式。保留字被引起來後可以用作識別符。

MySQL允許部分關鍵字用做未引起來的識別符，因為許多人以前曾使用過它們。下面列出了一些例子：

• ACTION
• BIT
• DATE
• ENUM
• NO
• TEXT
• TIME
• TIMESTAMP

（大資料工程師學習路徑）第五步 MySQL參考手冊中文版----MySQL資料類型