07、字符集與模式 · MySQL筆記

考慮多語言與移植的問題，選取合理的字符集。 1、字符集字符集就是一個字符 <-> 二進制字節的映射表。字符集是一套符號和編碼的規則，不論是在 oracle 數據庫還是在 mysql 數據庫，都存在字符集的選擇問題，而且如果在數據庫創建階段沒有正確選擇字符集，那么可能在后期需要更換字符集，而字符集的更換是代價比較高的操作，也存在一定的風險，所以我們推薦在應用開始階段，就按照需求正確的選擇合適的字符集，避免后期不必要的調整。字符集的選擇： ① 節省空間，建議在能夠完全滿足應用的前提下，盡量使用小的字符集。因為更小的字符集意味著能夠節省空間、減少網絡傳輸字節數，同時由于存儲空間的較小間接的提高了系統的性能。有很多字符集可以保存漢字，比如 utf8、gb2312、gbk、gb18030 等等，但是常用的是 gb2312 和 gbk。 ② 兼容性，因為 gb2312 字庫比 gbk 字庫小，有些偏僻字（例如:洺）不能保存，因此在選擇字符集的時候一定要權衡這些偏僻字在應用出現的幾率以及造成的影響。 ③ 在互聯網上，國際化的趨勢不可避免，且存儲空間已經越來海量化，因此推薦用 utf8，如果開發內網系統，如內部 OA 等，可以考慮 gbk。 2、校對集（排序規則）校對規則是某個字符集的排序規則，就是對字符集中的字符的"座次表"。一個字符集至少有一個校對集，默認都有一個校對集。 show charset; #查看所有字符集 show collation; #查看所有字符集的校對集 3、亂碼字符本來的字符集與展示時的字符集不一致就會發生亂碼。產生亂碼的過程： ① 客戶端在提交數據時需要告訴連接器這些數據的字符集（即客戶端字符集）； ② 連接器需要將數據按照數據庫聲明的字符集轉換一下，然后存起來； ③ 查詢數據庫的時候，會將數據從數據庫的字符集轉換成默認設置的查詢結果字符集；連接器的字符集一般使用比較大的字符集（gbk 或 utf8），避免丟失數據。三種字符集的設置： ① 客戶端（client）字符集 set character_set_client = utf8; ② 連接器（connection）字符集 set character_set_connection = utf8; ③ 查詢結果（results）字符集 set character_set_results = utf8; 同時設置以上三種字符集 set names gbk; 4、指定字符集方式 create database db1 charset utf8; create database db1 charset = utf8; create database db1 (default) character set utf8; 5、四個級別的編碼格式服務器級別如果為服務器指定了一個編碼格式，在創建數據庫的時候，如果沒有特殊說明，都是使用該編碼格式。數據庫級別如果為數據庫指定了一個編碼格式，在創建表的時候默認編碼格式就是數據庫的編碼格式，當然也可以指定某個表的編碼格式。數據表級別如果我們為某個表指定了自己的字符集，它將不繼承數據庫的字符集。數據列級別這個很少用，MySQL 也是為了保證和其他產品的兼容性才有這個古怪的規定。修改字符集： alter table tbl_name charset utf8; #修改表編碼格式 alter table tbl_name change 舊列名新列名列類型列級完整性約束; #修改列編碼格式 6、模式（SQL Mode） SQL Mode 定義了 MySQL 應支持的 SQL 語法、數據校驗等，可以完成不同嚴格程度的數據校驗，有效地保障數據準確性。 select @@sql_mode; #show variables like '%sql_mode%'; set [global|session] sql_mode = '模式名'; #global 表示在下次連接生效，session 表示本次連接生效。對于 CURD 在不同模式下得到的結果都是不同的，大部分情況都一致，三種常見的模式： ① ANSI，它等同于 REAL_AS_FLOAT PIPES_AS_CONCAT ANSI_QUOTES IGNORE_SPACE ANSI 等組合模式，這種模式使語法和行為更符合標準的SQL，這樣應用在不同數據庫之間進行遷移時，則不需要對業務 SQL 進行較大的修改。 ② STRICT_TRANS_TABLES（嚴格模式），它適用于事務表和非事務表。它是嚴格模式，不允許非法日期，也不允許超過字段長度的值插入字段中，對于插入不正確的值給出錯誤而非警告。 ③ TRADITIONAL，它等同于 STRICT_TRANS_TABLES STRICT_ALL_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE ERROR_FOR_DIVISION_BY_ZERO TRADITIONAL NO_AUTO_CREATE_USER 等組合模式，所以他也是嚴格模式，對于插入不正確的值是給出錯誤而非警告，可以應用在事務表和非事務表，用在事務表是，只要是出現錯誤就會立即回滾。