翻譯：MariaDB字元集和排序規則

来源:http://www.cnblogs.com/f-ck-need-u/archive/2017/09/26/7599446.html
-Advertisement-
Play Games

本文為mariadb官方手冊：SETTING CHARACTER SETS AND COLLATIONS的譯文。 原文：https://mariadb.com/kb/en/setting-character-sets-and-collations/我提交到MariaDB官方手冊的譯文：https:/ ...


本文為mariadb官方手冊：SETTING CHARACTER SETS AND COLLATIONS的譯文。

原文：https://mariadb.com/kb/en/setting-character-sets-and-collations/
我提交到MariaDB官方手冊的譯文：https://mariadb.com/kb/zh-cn/setting-character-sets-and-collations/

在MariaDB中，預設的字元集character set為latin1，預設的排序規則為latin1_swedish_ci(但不同的發行版可能會不同，例如Debian)。字元集和排序規則都可以從server端一直指定到欄位級別，client連接到server時也可以指定。當修改字元集但卻沒有指定排序規則時，將總是使用字元集的預設排序規則。

字元集和排序規則總是級聯向下的，所以當沒有為欄位指定排序規則時，將查找表的排序規則，同樣對於表來說會上查到資料庫，對資料庫來說會上查到server級。因此，可以使用極細粒度的字元集和排序規則來控制控制你的數據。

每種字元集的預設排序規則可使用SHOW COLLATION語句查看，例如查找latin2字元集的預設排序規則：

SHOW COLLATION LIKE 'latin2%';
+---------------------+---------+----+---------+----------+---------+
| Collation           | Charset | Id | Default | Compiled | Sortlen |
+---------------------+---------+----+---------+----------+---------+
| latin2_czech_cs     | latin2  |  2 |         | Yes      |       4 |
| latin2_general_ci   | latin2  |  9 | Yes     | Yes      |       1 |
| latin2_hungarian_ci | latin2  | 21 |         | Yes      |       1 |
| latin2_croatian_ci  | latin2  | 27 |         | Yes      |       1 |
| latin2_bin          | latin2  | 77 |         | Yes      |       1 |
+---------------------+---------+----+---------+----------+---------+

Server級別

可以設置系統變數character_set_server來改變預設的server級的字元集。該變數可以使用SET命令在啟動時或動態地設置：

SET character_set_server = 'latin2';

類似地，變數collation_server用於設置server級別的預設排序規則。

SET collation_server = 'latin2_czech_cs';

Database級別

CREATE DATABASE 和 ALTER DATABASE 語句中包含了可選的字元集、排序規則的設置子句。如果沒有設置字元集、排序規則，它們將使用server級別的預設值。

CREATE DATABASE czech_slovak_names 
  CHARACTER SET = 'keybcs2'
  COLLATE = 'keybcs2_bin';
ALTER DATABASE czech_slovak_names COLLATE = 'keybcs2_general_ci';

使用下麵的語句可以查看資料庫所使用的字元集：

SHOW CREATE DATABASE czech_slovak_names;
+--------------------+--------------------------------------------------------------------------------+
| Database           | Create Database                                                                |
+--------------------+--------------------------------------------------------------------------------+
| czech_slovak_names | CREATE DATABASE `czech_slovak_names` /*!40100 DEFAULT CHARACTER SET keybcs2 */ |
+--------------------+--------------------------------------------------------------------------------+

或者，使用下麵的語句可以查看各資料庫採用的字元集和排序規則：

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def          | czech_slovak_names | keybcs2                    | keybcs2_general_ci     | NULL     |
| def          | information_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | mysql              | latin1                     | latin1_swedish_ci      | NULL     |
| def          | performance_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | test               | latin1                     | latin1_swedish_ci      | NULL     |
+--------------+--------------------+----------------------------+------------------------+----------+

可以僅指定排序規則，由於每種排序規則都對應於一種字元集，因此會同時設置排序規則所對應的字元集。

CREATE DATABASE danish_names COLLATE 'utf8_danish_ci';

SHOW CREATE DATABASE danish_names;
+--------------+----------------------------------------------------------------------------------------------+
| Database     | Create Database                                                                              |
+--------------+----------------------------------------------------------------------------------------------+
| danish_names | CREATE DATABASE `danish_names` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_danish_ci */ |
+--------------+----------------------------------------------------------------------------------------------+

儘管可以動態地設置系統變數character_set_database和collation_database，但它們用於確定資料庫所使用的預設字元集和排序規則，應該儘量僅在server端進行設置。

Table級別

CREATE TABLE 和 ALTER TABLE 語句支持可選的字元集、排序規則設置子句，它們是MariaDB和MySQL對標準SQL語句的擴展。

CREATE TABLE english_names (id INT, name VARCHAR(40)) 
  CHARACTER SET 'utf8' 
  COLLATE 'utf8_icelandic_ci';

如果既沒有指定字元集也沒有指定排序規則，則採用資料庫的預設值。如果僅設置了字元集，將採用字元集的預設排序規則。如果僅設置了排序規則，則排序規則相關聯的字元集也會被設置。

ALTER TABLE table_name
 CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];

如果沒有指定排序規則，將使用字元集預設的排序規則。

對於VARCHAR或TEXT類型的欄位，為了保證新欄位足夠大以能夠存儲原欄位的大量字元，CONVERT TO CHARACTER SET可能會改變數據類型。

例如，某TEXT類型的欄位存儲ascii字元時由於每個字元僅占用一個位元組，因此該欄位可以存儲65,535個字元。如果該欄位轉換為UTF8，由於每個字元需要3個位元組，該欄位的數據類型將被轉換為MEDIUMTEXT類型以便能夠存儲所有原欄位的字元。

CONVERT TO CHARACTER SET binary將分別轉換CHAR、VARCHAR和TEXT欄位為BINARY、VARBINARY和BLOB，並且之後將不再具有字元集屬性，或者可以在以後使用CONVERT TO CHARACTER SET語句來改變該行為。

為了避免CONVERT TO CHARACTER SET子句改變數據類型，可以在單獨的欄位上使用MODIFY。例如：

ALTER TABLE table_name MODIFY ascii_text_column TEXT CHARACTER SET utf8;
ALTER TABLE table_name MODIFY ascii_varchar_column VARCHAR(M) CHARACTER SET utf8;

Column級別

同樣可以為欄位類型為CHAR、TEXT或VARCHAR的欄位設置字元集和排序規則。可以使用CREATE TABLE和ALTER TABLE語句進行設置——不像table級別的設置，column級別的設置是標準SQL所支持的。

CREATE TABLE european_names (
  croatian_names VARCHAR(40) COLLATE 'cp1250_croatian_ci',
  greek_names VARCHAR(40) CHARACTER SET 'greek');

如果既沒有指定字元集也沒有指定排序規則，將使用表的預設值。如果僅設置了字元集，排序規則將使用字元集的預設排序規則，如果僅設置了排序規則，則其對應的字元集也會被設置。

當使用ALTER TABLE改變欄位的字元集時，需要確保字元集可以和已有數據相容。MariaDB將儘可能地一一映射轉換字元數據，但無法轉換的數據可能會亂碼丟失。 可以使用SHOW CREATE TABLE語句，或者查詢INFORMATION_SCHEMA資料庫來查看欄位的字元集和排序規則所採用的值。

SHOW CREATE TABLE european_names\G
*************************** 1. row ***************************
       Table: european_names
Create Table: CREATE TABLE `european_names` (
  `croatian_names` varchar(40) CHARACTER SET cp1250 COLLATE cp1250_croatian_ci DEFAULT NULL,
  `greek_names` varchar(40) CHARACTER SET greek DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE 'european%'\G
*************************** 1. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: danish_names
              TABLE_NAME: european_names
             COLUMN_NAME: croatian_names
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 40
  CHARACTER_OCTET_LENGTH: 40
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: cp1250
          COLLATION_NAME: cp1250_croatian_ci
             COLUMN_TYPE: varchar(40)
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
*************************** 2. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: danish_names
              TABLE_NAME: european_names
             COLUMN_NAME: greek_names
        ORDINAL_POSITION: 2
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 40
  CHARACTER_OCTET_LENGTH: 40
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: greek
          COLLATION_NAME: greek_general_ci
             COLUMN_TYPE: varchar(40)
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT:

文件名

從MariaDB 5.1開始，系統變數character_set_filesystem可用來控制對給定字元串的文件名解析。它會影響的下麵語句和函數：

  • SELECT INTO DUMPFILE
  • SELECT INTO OUTFILE
  • LOAD DATA INFILE
  • LOAD XML
  • LOAD_FILE()

字面符號(譯者註：可理解為常量、普通字元串或文本字元)

預設情況下，字元集和排序規則通過系統變數character_set_connection和collation_connection來決定使用的字面符號。但是可以顯式地指定它們：

[_charset_name]'string' [COLLATE collation_name]

對於沒有字元集introducer的字元集來說，它的字元串符號由系統變數character_set_connection決定。

該查詢：

SELECT CHARSET('a'), @@character_set_connection;

總是會為兩列返回相同的字元集名稱。

character_set_client和character_set_connection一般會被設置為相同的值(例如在三次握手期間，或使用SET NAMES進行了設置)。但允許設置為不同值。

示例

設置@@character_set_client和@@character_set_connection為不同的值時可能很有用處：

示例 1：

假設我們在utf8的資料庫中創建下麵的表：

CREATE TABLE t1 (a VARCHAR(10)) CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO t1 VALUES ('oe'),('ö');

現在使用"mysql.exe"連接，它會使用DOS的字元集(西歐的機器上是cp850)，如果想要根據德國電話簿規則獲取等於"ö"的所有記錄。

使用下麵的語句：

SET @@character_set_client=cp850, @@character_set_connection=utf8; 
SELECT a FROM t1 WHERE a='ö' COLLATE utf8_german2_ci;

它將返回：

+------+
| a    |
+------+
| oe   |
| ö    |
+------+

工作方式如下：

  1. 客戶端使用cp850發送查詢語句。
  2. 服務端解析查詢語句時，將把'ö'從@@character_set_client (cp850)轉換為@@character_set_connection (utf8)的文本字元。
  3. 服務端對該文本字元應用排序規則"utf8_germal2_ci"。
  4. 服務端使用utf8_german2_ci進行字元比較。

註意，如果重寫為如下腳本：

SET NAMES cp850;
SELECT a FROM t1 WHERE a='ö' COLLATE utf8_german2_ci;

將報錯：

ERROR 1253 (42000): COLLATION 'utf8_german2_ci' is not valid for CHARACTER SET 'cp850'

因為：

  • 在第2步中，將不會轉換為utf8的文本字元，而是轉換為cp850的文本字元。
  • 在第3步中，服務端無法對cp850的字元串應用排序規則utf8_german2_ci。

示例 2:

繼續假設我們的資料庫為utf8，並使用西歐機器上的"mysql.exe"進行連接。

我們這樣做：

SET @@character_set_client=cp850, @@character_set_connection=utf8;
CREATE TABLE t2 AS SELECT 'ö';

這將會創建一張包含VARCHAR(1) CHARACTER SET utf8欄位類型的表。

註意，如果查詢重寫為：

SET NAMES cp850;
CREATE TABLE t2 AS SELECT 'ö';

創建的表中的欄位將為VARCHAR(1) CHARACTER SET cp850類型，這可能不是我們所期望的。

N

同樣, 可以使用首碼N或n來轉換文本字元為國際字元集(MariaDB中為utf8)。

例如：

SELECT _latin2 'Müller';
+-----------+
| MĂźller   |
+-----------+
| MĂźller   |
+-----------+
SELECT CHARSET(N'a string');
+----------------------+
| CHARSET(N'a string') |
+----------------------+
| utf8                 |
+----------------------+
SELECT 'Mueller' = 'Müller' COLLATE 'latin1_german2_ci';
+---------------------------------------------------+
| 'Mueller' = 'Müller' COLLATE 'latin1_german2_ci'  |
+---------------------------------------------------+
|                                                 1 |
+---------------------------------------------------+

存儲過程和視圖

當創建存儲過程或視圖時，其內出現的文本字元預設使用系統變數character_set_connection和collation_connection指定的字元集和排序規則。可以使用SHOW CREATE語句獲取所使用的值。要改變已存在存儲過程、視圖中的文本字元的字元集，需要刪除存儲程式然後重建。

對於存儲過程的參數和返回值，可以通過CHARACTER SET和COLLATE子句來指定其使用的字元集和排序規則。在MariaDB 5.5之前不支持指定排序規則。

下麵的示例中展示了創建存儲程式時所使用的字元集和排序規則。

SET @@local.character_set_connection='latin1';

DELIMITER ||
CREATE PROCEDURE `test`.`x`()
BEGIN
    SELECT CHARSET('x');
END;
||
Query OK, 0 rows affected (0.00 sec)

DELIMITER ;
SET @@local.character_set_connection='utf8';

CALL `test`.`x`();
+--------------+
| CHARSET('x') |
+--------------+
| latin1       |
+--------------+

下麵的示例中展示瞭如何指定函數的參數和返回值的字元集和排序規則：

CREATE FUNCTION `test`.`y`(`str` TEXT CHARACTER SET utf8 COLLATE utf8_bin)
    RETURNS TEXT CHARACTER SET latin1 COLLATE latin1_bin
BEGIN
    SET @param_coll = COLLATION(`str`);
    RETURN `str`;
END;

-- 返回值的排序規則:
SELECT COLLATION(`test`.`y`('Hello, planet!'));
+-----------------------------------------+
| COLLATION(`test`.`y`('Hello, planet!')) |
+-----------------------------------------+
| latin1_bin                              |
+-----------------------------------------+

-- 參數的排序規則：
SELECT @param_coll;
+-------------+
| @param_coll |
+-------------+
| utf8_bin    |
+-------------+

示例：更改預設的字元集為UTF-8

要改變預設的字元集latin1為UTF-8，需要在配置文件my.cnf中進行如下設置：

[client]
...
default-character-set=utf8
...
[mysql]
...
default-character-set=utf8
...
[mysqld]
...
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
...

註意，選項default-character-set是一個客戶端選項，而非服務端選項。

 

回到Linux系列文章大綱：http://www.cnblogs.com/f-ck-need-u/p/7048359.html

回到資料庫系列文章大綱：http://www.cnblogs.com/f-ck-need-u/p/7586194.html

轉載請註明出處：http://www.cnblogs.com/f-ck-need-u/p/7599446.html

註：若您覺得這篇文章還不錯請點擊右下角推薦，您的支持能激發作者更大的寫作熱情，非常感謝！


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • SparkRDD簡介/常用運算元/依賴/緩存
    SparkRDD簡介/常用運算元/依賴/緩存 RDD簡介 RDD（Resilient Distributed Dataset）叫做分散式數據集，是Spark中最基本的數據抽象，它代表一個不可變、可分區、裡面的元素可並行計算的集合。RDD是一個類 RDD的屬性 1.一個列表，存儲存取每個Partitio ...
  • oracle中number數據類型最簡單明瞭解釋
    NUMBER （p,s） p和s範圍： p 1-38 s -84-127 number(p,s),s大於0，表示有效位最大為p，小數位最多為s，小數點右邊s位置開始四捨五入，若s>p，小數點右側至少有s-p個0填充（必須從小數點處開始並連續）。 舉例： number(2,1) 有效位最大為2，小數點 ...
  • Oracle中用exp/imp命令參數詳解【轉】
    Oracle中用exp/imp命令參數詳解 【用 exp 數 據 導 出】：1 將資料庫TEST完全導出,用戶名system 密碼manager 導出到D:\daochu.dmp中 exp system/manager@TEST rows=y indexes=y compress=n buffer= ...
  • MySQL之索引
    索引是資料庫中用來提高性能的最常用的工具，本次博客就來介紹一下索引，mysql版本5.7.19。 索引概述 所有MySQL列類型都可以被索引，對相關的列使用索引是可以提高SELECT操作性能的最佳途徑。MyISAM和InnoDB存儲引擎預設是BTREE索引。其實索引就像是一個字典的目錄，你可以通過索 ...
  • Microsoft SQL Server學習（二）
    [TOC] 關於資料庫的語法： 1.創建資料庫 create database 資料庫名 on primary （主文件屬性（name，filename，size等）） 用逗號隔開次要主要文件和次要文件 (次要文件屬性(name,filename,size等)) log on （日誌文件屬性（nam ...
  • 資料庫記錄刪除方式
    刪除資料庫記錄是一個非常常見的需求，當數據失去價值時，我們便會刪除它，但是如果操作不當，往往就會把一些有價值的數據誤刪掉，造成重要數據的丟失，合理採用刪除方式才能更好地利用數據資源。 ...
  • PIVOT 行列相轉
    先介紹一下英文釋義： pivot 英 ['pɪvət] 美 ['pɪvət] n. 樞軸；中心點；旋轉運動 vt. 以…為中心旋轉；把…置於樞軸上 vi. 在樞軸上轉動；隨…轉移 adj. 樞軸的；關鍵的 從上面就不難看出這個函數是做什麼的，旋轉，轉換，用於列和行之間對數據進行旋轉或透視轉換，同時執 ...
  • 翻譯：CREATE DATABASE語句
    本文為mariadb官方手冊：CREATE DATABASE的譯文。 原文：https://mariadb.com/kb/en/create-database/我提交到MariaDB官方手冊的譯文：https://mariadb.com/kb/zh-cn/create-database/ 語法 描述 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類