`

MariaDB_Setting Character Sets and Collations

 
阅读更多

via: https://mariadb.com/kb/en/setting-character-sets-and-collations/

 

In MariaDB, the default character set is latin1, and the default collation is latin1_swedish_ci. Both character sets and collations can be specified from the server right down to the column level, as well as for client-server connections. When changing a character set and not specifying a collation, the default collation for the new character set is always used. These can be viewed with the SHOW COLLATION statement, for example, to find the default collation for the latin2 character set:

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 |
+---------------------+---------+----+---------+----------+---------+

Character sets and collations always cascade down, so a column without a specified collation will look for the table default, the table for the database, and the database for the server. It's therefore possible to have extremely fine-grained control over all the character sets and collations used in your data.

Server level

The character_set_server system variable can be used to change the default server character set. It can be set both on startup or dynamically, with the SET command:

SET character_set_server = 'latin2';

Similarly, the collation_server variable is used for setting the default server collation.

SET collation_server = 'latin2_czech_cs';

Database level

The CREATE DATABASE and ALTER DATABASE statements have optional character set and collation clauses. If these are left out, the server defaults are used.

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

To determine the default character set used by a database, use:

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

or alternatively, for the character set and collation:

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     |
+--------------+--------------------+----------------------------+------------------------+----------+

It is also possible to specify only the collation, and, since each collation only applies to one character set, the associated character set will automatically be specified.

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 */ |
+--------------+----------------------------------------------------------------------------------------------+

Although there are character_set_database and collation_database system variables which can be set dynamically, these are used for determining the character set and collation for the default database, and should only be set by the server.

Table level

The CREATE TABLE and ALTER TABLE statements support optional character set and collation clauses, a MariaDB and MySQL extension to standard SQL.

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

If neither character set nor collation is provided, the database default will be used. If only the character set is provided, the default collation for that character set will be used. If only the collation is provided, the associated character set will be used.

Column level

Character sets and collations can also be specified for columns that are character types - CHAR, TEXT or VARCHAR. The CREATE TABLE and ALTER TABLE statements support optional character set and collation clauses for this purpose - unlike those at the table level, the column level definitions are standard SQL.

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

If neither collation nor character set is provided, the table default is used. If only the character set is specified, that character set's default collation is used, while if only the collation is specified, the associated character set is used.

When using ALTER TABLE to change a column's character set, you need to ensure the character sets are compatible with your data. MariaDB will map the data as best it can, but it's possible to lose data if care is not taken.

The SHOW CREATE TABLE statement or INFORMATION SCHEMA database can be used to determine column character sets and collations.

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:

Literals

By default, the character set and collation used for literals is determined by the character_set_connection andcollation_connection system variables. However, they can also be specified explicitly:

[_charset_name]'string' [COLLATE collation_name]

Also, N or n can be used as prefix to convert a literal into the National Character set (which in MariaDB is always utf8).

For example:

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 |
+---------------------------------------------------+

Stored programs and views

The literals which occur in stored programs and views, by default, use the character set and collation which was specified by the character_set_connection and collation_connection system variables when the stored program was created. These values can be seen using the SHOW CREATE statements. To change the character sets used for literals in an existing stored program, it is necessary to drop and recreate the stored program.

For stored routines parameters and return values, a character set and a collation can be specified via the CHARACTER SET and COLLATE clauses. Before 5.5, specifying a collation was not supported.

The following example shows that the character set and collation are determined at the time of creation:

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       |
+--------------+

The following example shows how to specify a function parameters character set and collation:

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;

-- return value's collation:
SELECT COLLATION(`test`.`y`('Hello, planet!'));
+-----------------------------------------+
| COLLATION(`test`.`y`('Hello, planet!')) |
+-----------------------------------------+
| latin1_bin                              |
+-----------------------------------------+

-- parameter's collation:
SELECT @param_coll;
+-------------+
| @param_coll |
+-------------+
| utf8_bin    |
+-------------+

 

分享到:
评论

相关推荐

    navicat150_mariadb_cs_x64.exe

    navicat150_mariadb_cs_x64.exe 一款专门为mariadb 数据库使用的navicat工具

    Mariadb_install_win.rar

    标题"Mariadb_install_win.rar"表明这是一个关于在Windows操作系统上安装MariaDB的压缩包文件,特别是通过批处理(BAT)脚本来实现一键安装的过程。MariaDB是一个开源的关系型数据库管理系统,它是MySQL的一个分支,...

    (34条消息) CentOS 7 安装mariadb_centos7安装mariadb_托塔雨天王的博客-CSDN博客.mhtml

    (34条消息) CentOS 7 安装mariadb_centos7安装mariadb_托塔雨天王的博客-CSDN博客.mhtml

    mariadb_deploy.sh

    Centos7自动安装mariadb并设置密码允许用户远程登录脚本

    Automatic_installation_for_MariaDB_on_Linux(MariaD_ansible-

    Automatic_installation_for_MariaDB_on_Linux(MariaD_ansible-mariadb

    navicat110_mariadb_cs_x86.zip

    标题中的"navicat110_mariadb_cs_x86.zip"指向的是一款专为32位Windows系统设计的Navicat for MariaDB的11.0版本。这款工具以其直观的界面和强大的功能,让数据库管理变得轻松而高效。 首先,Navicat for MariaDB是...

    Mariadb_install_linux.rar

    本文将详细介绍如何使用提供的shell脚本"Mariadb_install_linux"在CentOS 7上进行MariaDB的安装。MariaDB是MySQL的一个分支,由MySQL的创始人创建,旨在保持开源和不受Oracle公司控制。 1. **CentOS 7系统准备**: ...

    mariadb_10.0.14 龙芯cpu 安装包

    mariadb_10.0.14 龙芯cpu 安装包,已经适配了3A3000,3a4000. 安装后需要命令好启动。 安装路径为\usr\local\mysql。 仅供研究使用,请勿商用。

    mariadb_v10.5.8_winx64_itmop.com.zip

    这个“mariadb_v10.5.8_winx64_itmop.com.zip”压缩包包含了在Windows 64位系统上运行的MariaDB 10.5.8版本。 MariaDB 10.5.8是该数据库系统的一个重要版本,提供了许多新特性和改进。以下是一些关键知识点: 1. *...

    MariaDB_Query_Pro_V6.0.0.1

    A simple tool, which can help you bring all data stored in Excel Data Files into MariaDB, and then you can handle your data in MariaDB via this tool through Excel. Just try it!

    NetBackup82_MariaDB_AdminGuide.pdf

    VERITAS NetBackup 8.2 中文管理指南是 Veritas Technologies LLC 发布的一份关于 VERITAS NetBackup for MariaDB 的管理指南,该指南适用于 Windows 和 Linux 平台,版本为 8.2。本指南最后一次更新时间为 2019 年 ...

    mariadb_cluster1:Docker 容器上的 MariaDB 集群

    Docker pull jsdizon/mariadb_cluster1 创建图像。 git clone https://github.com/jsdizon/mariadb_cluster1.git docker build -t mariadbcluster . 单个 Docker 服务器 假设我们有一个 docker 服务器,我们将...

    NetBackup812_MariaDB_AdminGuide.pdf

    Veritas NetBackup 8.1.2 是一款强大的数据保护解决方案,特别针对MariaDB数据库管理系统设计的管理员指南。本指南提供了在Windows和Linux操作系统上管理MariaDB备份的详细信息,帮助用户确保数据的安全性和可恢复性...

    navicat150_mariadb_en.dmg navicat-for-mariadb: 15.0.12

    用于MariaDB的Navicat为MariaDB数据库管理和开发提供了本机环境。连接到本地/远程MariaDB服务器,并与Amazon RDS兼容。适用于MariaDB的Navicat支持大多数附加功能,例如新的存储引擎,微秒,虚拟列等。

    NetBackup83_MariaDB_AdminGuide.pdf

    Veritas NetBackup 8.3 是一款强大的数据保护解决方案,专为Windows和Linux环境设计,旨在为MariaDB数据库提供安全且高效的备份与恢复服务。MariaDB是一款开源的关系型数据库管理系统,广泛应用于各种业务场景,因此...

    NetBackup811_MariaDB_AdminGuide.pdf

    Veritas NetBackup 8.1.1 for MariaDB Administrator's Guide是专为在Windows和Linux平台上管理和保护MariaDB数据库设计的一份详细指南。Veritas Technologies LLC是一家知名的备份和恢复解决方案提供商,其...

    mariadb_mysql_CH_ZN.txt

    Wamp数据库帮助中文

    CENTOS7.X下MariaDB_Galera集群安装及配置真实可用

    按照解压后Galera.txt中的操作一步步做,20分钟安装配置好CENTOS7.X(现本人在用的是7.6)下的MariaDB 10.3.12(最新稳定版2019.1.7发布)的Galera 25.24的双主集群。 MariaDB 为Mysql的开原版,百分百兼容Mysql,双...

Global site tag (gtag.js) - Google Analytics