[MySQL Reference Manual] 10 全球化

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 10.全球化 本章主要介绍全球化,包含国际化和本地化,的一些问题: ·         MySQL在语句中支持的字符集 ·         如何为服务配置不同的字符集 ·         选择错误信息的语言 ·         如何设置服务的时区和每个连接的时区 ·         选择本土化的日期和月份名 10.

10.全球化

本章主要介绍全球化,包含国际化和本地化,的一些问题:

·         MySQL在语句中支持的字符集

·         如何为服务配置不同的字符集

·         选择错误信息的语言

·         如何设置服务的时区和每个连接的时区

·         选择本土化的日期和月份名

10.全球化... 1

10.1 字符集的支持... 2

10.1.1 字符集和排序规则... 2

10.1.2 mysql中的字符集和排序规则... 3

10.1.3 制定字符集和排序规则... 3

10.1.3.1 服务字符集和排序规则... 3

10.1.3.2 数据库级字符集和排序规则... 3

10.1.3.3 表的字符集和排序规则... 4

10.1.3.4 列的字符集和排序规则... 4

10.1.3.5 字符串常量的字符集和排序规则... 5

10.1.3.6 National Character Set6

10.1.3.7 字符集和排序规则设置的例子... 6

10.1.3.8 和其他数据的兼容... 6

10.1.4 连接的字符集和排序规则... 7

10.1.5 为应用程序配置字符集和排序规则... 8

10.1.5.1 为每个数据指定字符集和排序规则... 8

10.1.5.2 在服务启动是配置字符集... 8

10.1.5.3 Mysql配置的时候设置字符集和排序规则... 8

10.1.6 错误信息的字符集... 9

10.1.7 排序规则... 9

10.1.7.1 排序规则名... 9

10.1.7.2 在语句中使用排序规则... 9

10.1.7.3 COLLATE子句... 10

10.1.7.4 排序规则必须要一个正确的字符集... 10

10.1.7.5 表达式中的排序规则... 10

10.1.7.6 _binbinary. 11

10.1.7.7 BINARY操作... 13

10.1.7.8 Collation的影响例子... 13

10.1.7.9 排序规则和INFORMATION_SCHEMA查找... 13

10.1.8 字符目录(String Repertoire)15

10.1.9 受字符集影响的操作... 16

10.1.9.1 结果字符串... 16

10.1.9.2 CONVERT()CAST()16

10.1.9.3 SHOW语句和INFORMATION_SCHEMA.. 16

10.1.10 Unicode支持... 16

10.1.10.1 ucs2字符集... 17

10.1.10.2 utf16字符集... 17

10.1.10.3 utf16le字符集... 17

10.1.10.4 utf32字符集... 17

10.1.10.5 utf8字符集... 17

10.1.10.6 utf8mb3字符集... 18

10.1.10.7 utf8mb4字符集... 18

10.1.11 更新unicode的支持... 18

10.1.12 元数据为utf8. 19

10.1.13 列字符集转化... 19

10.1.14 MySQL支持的字符集和排序规则... 20

10.2 设置错误信息语言... 20

10.3 增加一个字符集... 20

10.4 为字符集增加一个排序规则... 20

10.5 配置字符集... 20

10.6 MySQL时区的支持... 20

10.6.1 跟上时区修改... 21

10.6.2 时区闰秒支持(Time Zone Leap Seconad Support)22

10.7 MySQL Server本地化支持... 23

 

10.1 字符集的支持

Mysql可以让你使用不同的字符集来保存数据,可以再多个排序规则下比较数据。可以再服务,数据库,表和列级别设置字符集。本章回答以下几个问题:

·         使用什么字符集和排序规则

·         多个级别的系统默认字符集的分配

·         设置字符集和排序规则的设置语法

·         会收到影响的函数和操作

·         Unicode 的支持

·         可用的字符集和排序规则

字符集不单单影响数据存储,也会影响clientserver之间的交互。可以使用set names来设置clientserver之间交互使用的字符集。

10.1.1 字符集和排序规则

字符集是一堆符号和编码,排序规则是字符集的比较规则。比如比较“A”“B”最简单的方法是比较编码,这种排序规则也是最简单的排序规则,成为binary排序规则。

对于a=A,b=B我们成为大小写不敏感的排序规则。每个字符集有很多个排序规则。

mysql可以做到以下一些:

1.可以以多个字符集保存字符串

2.比较字符串可以使用不同的排序规则

3.在同一个服务中可以混合多个字符集和排序规则

4.字符集和排序规则有很多级别(server,db,tb.column)

10.1.2 mysql中的字符集和排序规则

Mysql可以支持多个字符集,可以使用SHOW CHARACTER SET来显示。一个字符集有多个排序规则,至少一个,可以使用SHOW COLLATION,查看。

排序规则有以下一些特性:

·         2个不同的字符集不可能拥有同一个排序规则

·         每个字符集都有一个默认排序规则

·         排序规则命名,一般是字符集名,语言名,后缀。后缀有_ci(大小写不明感),_cs(大小写敏感),_bin(binary)

避免使用错的排序规则,保证制定的排序规则给出的顺序是你要的。

10.1.3 制定字符集和排序规则

4个级别上会设置默认的字符集和排序规则,CHARACTER SET可以用来设置指定字符集。CHARSETCHARACTER SET的另外一种写法。

字符集不单单影响数据存储,也会影响客户端和服务器之间的交互。

10.1.3.1 服务字符集和排序规则

服务级别的字符集和排序规则可以在命令行和配置文件中设置。使用—character-set-server设置字符集,--collation-server设置排序规则。

shell> mysqld

shell> mysqld --character-set-server=latin1

shell> mysqld --character-set-server=latin1 \

           --collation-server=latin1_swedish_ci

如果要修改默认的服务级的字符集和排序规则,可以通过重编译源代码进行。

shell> cmake . -DDEFAULT_CHARSET=latin1

shell> cmake . -DDEFAULT_CHARSET=latin1 \

           -DDEFAULT_COLLATION=latin1_german1_ci

MysqldCMake都会验证字符集和排序规则是否可用,如果不可用每个程序都会显示错误信息和中断。

create database没有指定,会使用服务的字符集和排序规则。Character_set_servercollation_server指定了服务的字符集和排序规则。这些变量可以再运行时被修改。

10.1.3.2 数据库级字符集和排序规则

数据库级的排序规则可以使用create databasealterdatabase来指定。

CREATE DATABASE db_name

    [[DEFAULT] CHARACTER SET charset_name]

    [[DEFAULT] COLLATE collation_name]

 

ALTER DATABASE db_name

    [[DEFAULT] CHARACTER SET charset_name]

    [[DEFAULT] COLLATE collation_name]

CHARACTER_SET,COLLATE子句可以指定不同于服务的字符集和排序规则。

CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;

Mysql以以下方式来获取字符集和排序规则:

·         如果指定了CHARACTER SET XCOLLATE Y,那么会使用字符集x,排序规则y

·         如果指定了字符集x没有指定排序规则,那么使用默认的排序规则。

·         如果指定了排序规则没有指定字符集,那么使用和排序规则相关的字符集。

·         否则使用服务的字符集和排序规则。

和创建数据库一样,创建表的时候没有指定就使用数据库级的字符集和排序规则。Character_set_databasecollation_database系统变量,当有数据库时,为数据库的字符集和排序规则,如果没有数据库时是server的字符集和排序规则。

10.1.3.3 表的字符集和排序规则

表的排序规则可以通过create tablealter table来修改。

CREATE TABLE tbl_name (column_list)

    [[DEFAULT] CHARACTER SET charset_name]

    [COLLATE collation_name]]

 

ALTER TABLE tbl_name

    [[DEFAULT] CHARACTER SET charset_name]

    [COLLATE collation_name]

表选择字符集和排序规则和数据库一致。

如果创建表时,表没有指定列的字符集和排序规则,那么就用表的字符集和排序规则。

10.1.3.4 列的字符集和排序规则

列的字符集和排序规则通过create tablealter table来指定。

col_name {CHAR | VARCHAR | TEXT} (col_length)

    [CHARACTER SET charset_name]

    [COLLATE collation_name]

col_name {ENUM | SET} (val_list)

    [CHARACTER SET charset_name]

    [COLLATE collation_name]

如:

CREATE TABLE t1

(

    col1 VARCHAR(5)

      CHARACTER SET latin1

      COLLATE latin1_german1_ci

);

 

ALTER TABLE t1 MODIFY

    col1 VARCHAR(5)

      CHARACTER SET latin1

      COLLATE latin1_swedish_ci;

Mysql为列选择字符集和排序规则的方法和表一样。

如果alter table从一个字符集转为另外一个字符集的时候,如果字符集不兼容那么就有可能丢失数据。

10.1.3.5 字符串常量的字符集和排序规则

每个字符串都有一个字符集和排序规则。指定字符串常量字符集的方法:

[_charset_name]'string' [COLLATE collation_name]

例如:

SELECT 'string';

SELECT _latin1'string';

SELECT _latin1'string' COLLATE latin1_danish_ci;

如果什么都没有指定,那么使用连接的字符集和排序规则,character_set_connectioncollation_connection决定。

Mysql选择字符集和排序规则的方式如下:

·         如果指定了字符集x和排序规则y,那么就是用x,y

·         如果指定了字符集没有指定排序规则,那么使用xx的默认排序规则

·         否则使用变量character_set_connectioncollation_connection

前面指定的字符集并不影响转移字符,转移字符是受character_set_connection影响。

mysql> SET NAMES latin1;

Query OK, 0 rows affected (0.01 sec)

 

mysql> SELECT HEX('à\n'), HEX(_sjis'à\n');

+------------+-----------------+

| HEX('à\n') | HEX(_sjis'à\n') |

+------------+-----------------+

| E00A       | E00A            |

+------------+-----------------+

1 row in set (0.00 sec)

mysql> SET NAMES sjis;

Query OK, 0 rows affected (0.00 sec)

 

mysql> SELECT HEX('à\n'), HEX(_latin1'à\n');

+------------+-------------------+

| HEX('à\n') | HEX(_latin1'à\n') |

+------------+-------------------+

| E05C6E     | E05C6E            |

+------------+-------------------+

1 row in set (0.04 sec)

在字符集sjis中“\”并不认为是转移“\”=5C,“n”=6E

10.1.3.6 National Character Set

标准SQL定义NCHAR或者 NATIONAL CHAR来说明这个char列要使用预定于的字符集。Mysql 5.6使用utf8作为预定义的字符集。可以通过N’some text’来创建national 字符集内的字符串。

10.1.3.7 字符集和排序规则设置的例子

1.设置列和表的字符集和排序规则

CREATE TABLE t1

(

    c1 CHAR(10) CHARACTER SET latin1 COLLATE latin1_german1_ci

) DEFAULT CHARACTER SET latin2 COLLATE latin2_bin;

2.列不带排序规则

CREATE TABLE t1

(

    c1 CHAR(10) CHARACTER SET latin1

) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;

3.列不带字符集和排序规则

CREATE TABLE t1

(

    c1 CHAR(10)

) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;

4.数据库设置排序规则和字符集,表和列不设置

CREATE DATABASE d1

    DEFAULT CHARACTER SET latin2 COLLATE latin2_czech_ci;

USE d1;

CREATE TABLE t1

(

    c1 CHAR(10)

);

10.1.3.8 和其他数据的兼容

10.1.4 连接的字符集和排序规则

连接的字符集和排序规则主要说明一下一些问题:

·         当客户端发送前语句的时候使用什么字符集,character_set_client

·         语句在传输过程中是什么字符集,character_set_connection,和排序规则collation_connection,然后会从character_set_client转化为character_set_connection

·         在传输错误或者结果到客户端前字符集是什么,character_set_results指定了返回前的字符集。

2个语句会影响和连接相关的字符集变量

·         SET NAMES ‘charset_name’[COLLATE ‘collation_name’]

SET NAMES相当于以下3个语句

SET character_set_client = charset_name;

SET character_set_results = charset_name;

SET character_set_connection = charset_name;

·         使用SET语句设置,如:

SET character_set_client = charset_name;

SET character_set_results = charset_name;

SET collation_connection = @@collation_database;

Mysql的一些客户端程序如mysqlMySQLadminmysqlcheckmysqlimportmysqlshow决定默认字符集规则如下:

·         在没有信息的情况下使用Latin1字符集

·         程序可以自动识别OS上使用的字符集,如LANG或者LC_ALL。或者windows上的codepage来判断

·         程序也支持使用—default-character-set选项,显示的设置字符集。

在程序连接服务的时候,使用程序要的字符集发送给服务,然后何止,character_set_client,character_set_resultscharacter_set_connection系统变量。事实上可以使用set names来设置。

如果默认的和你想要的不一样可以使用set names来设置或者加上参数—default-caracter-set,或者加在配置文件中。

[mysql]

default-character-set=koi8r

如果可以自动重连,可以使用charset命令:

mysql> charset utf8

Charset changed

Charset命令使用set names,然后修改mysql的默认字符集,然后重连。

假设列column1定义为char(5) CHARACTER SET latin2。如果没有设置set namesset character set。那么mysql会以column1的字符集和排序规则来发送数据,如果设置了set names latin1,那么会把latin2转化为latin1然后再发送。

如果要尝试直接发送:

SET character_set_results = NULL;

10.1.5 为应用程序配置字符集和排序规则

应用程序存数据使用默认的字符集和排序规则。如果应用程序要不同的排序规则和字符集,可以通过几个方式来配置:

·         为每个数据库指定一个字符集

·         在服务启动的时候指定一个字符集

·         mysql编译的时候配置字符集

如果所有的或者大多数的应用程序使用相同的字符集,可以再服务启动的时候或者配置mysql的时候设置。

10.1.5.1 为每个数据指定字符集和排序规则

create database中设置如:

CREATE DATABASE mydb

  DEFAULT CHARACTER SET utf8

  DEFAULT COLLATE utf8_general_ci;

应用程序在使用这个数据库的时候,每次连接都要配置可以指定—default-character-set=utf8或者set names=‘utf8’

如果修改了数据库的字符集和排序规则,那么所有的过程都要被重建。

10.1.5.2 在服务启动是配置字符集

在服务启动的时候,使用命令行—character-set-server—collation-server,或者设置配置文件:

[mysqld]

character-set-server=utf8

collation-server=utf8_general_ci

这些配置是服务级别的,所以在创建数据库的时候会以这个为默认的字符集和排序规则。一旦设置了之后,每个连接可能都要设置一下排序规则,那么可以再系统启动的时候设置—init_connect=”SET NAMES ‘utf8’”,这样每次连接的时候都会运行一下set names命令。

10.1.5.3 Mysql配置的时候设置字符集和排序规则

设置方法:

shell> cmake . -DDEFAULT_CHARSET=utf8 \

           -DDEFAULT_COLLATION=utf8_general_ci

10.1.6 错误信息的字符集

mysql 5.6,服务使用utf8构建消息,并且通过character_set_results系统变量来返回。

服务构建错误消息的过程:

·         消息模板使用utf8

·         然后消息中的参数被替换

o    如表名,列名会被原样复制

o    如果是字符串那么会被转化成utf8

o    如果是二进制串恢复0x200x7e之间的值,其他的会被加上前缀\x

o    Duplicate entry 'A\xC3\x9F' for key 1

如果构建完就会发送给client,字符集会从utf8转化为character_set_results系统变量。如果变量为null或者binary不会发送转化。

如果字符在character_set_results上没办法体现,某些字符可能发生某些编码。编码使用unicode指针值:

·         如果字符在BMP(Basic Multilingual Plane)范围内(0x0000-0xffff)写成\nnnn

·         如果再范围之外室友\+nnnnnn

客户端可以控制character_set_results来控制接受的字符串的字符集。

10.1.7 排序规则

10.1.7.1 排序规则名

排序规则名一般以_ci,_cs,_bin结尾

10.1.7.2 在语句中使用排序规则

·  With ORDER BY:

SELECT k

FROM t1

ORDER BY k COLLATE latin1_german2_ci;

·  With AS:

SELECT k COLLATE latin1_german2_ci AS k1

FROM t1

ORDER BY k1;

·  With GROUP BY:

SELECT k

FROM t1

GROUP BY k COLLATE latin1_german2_ci;

·  With aggregate functions:

SELECT MAX(k COLLATE latin1_german2_ci)

FROM t1;

·  With DISTINCT:

SELECT DISTINCT k COLLATE latin1_german2_ci

FROM t1;

·  With WHERE:

     SELECT *

     FROM t1

     WHERE _latin1 'Müller' COLLATE latin1_german2_ci = k;

     SELECT *

     FROM t1

     WHERE k LIKE _latin1 'Müller' COLLATE latin1_german2_ci;

·  With HAVING:

SELECT k

FROM t1

GROUP BY k

HAVING k = _latin1 'Müller' COLLATE latin1_german2_ci;

 

10.1.7.3 COLLATE子句

COLLATE||优先级要高,所以下面语句是等价的:

x || y COLLATE z

x || (y COLLATE z)

10.1.7.4 排序规则必须要一个正确的字符集

一个字符集有多个排序规则,但是一个排序规则只能对应到一个字符集,因此如果出现以下情况那么就会报错:

mysql> SELECT _latin1 'x' COLLATE latin2_bin;

ERROR 1253 (42000): COLLATION 'latin2_bin' is not valid

for CHARACTER SET 'latin1'

10.1.7.5 表达式中的排序规则

排序规则的主要用处是用来比较,如:

SELECT x FROM T ORDER BY x;

SELECT x FROM T WHERE x = x;

SELECT DISTINCT x FROM T;

如果是2个不同的操作数,可能排序规则会歧义如:

SELECT x FROM T WHERE x = 'Y';

出现这种情况标准的sql使用可压缩性规则来确定使用什么字符集。可压缩性值有以下决定:

·         一个现实的COLLATE子句的可压缩性值为0

·         并列的2个字符串有2个不同的排序规则,那么可压缩性为1

·         列或者存储的参数的排序规则可压缩性为2

·         系统常量,或者USER(),VERSION()的可压缩性为3

·         字符串常量的可压缩性值为4

·         Null的可压缩性值为5

Mysql使用可压缩性值来解决这个问题:

·         使用可压缩值最低的

·         如果吹按可压缩值相同

o    如果2边都是unicode或者都不是那么就报错

o    如果一边是unicode,一边不是那么传为非unicode

o    如果操作字符集相同,但是排序规则不同,比如_bin混合了_ci,_cs那么使用_bin

可压缩性值,可以通过coercibility查看

对于使用concat隐式转化的字符集和排序规则有连接的字符集和排序规则决定。

10.1.7.6 _binbinary

串有2中,一种是字符串(Nonbinary string),一种是二进制串(binary string)

Nonbinary string以数据类型char,varchar,text保存,有字符集和排序规则。

Binary stringbinaryvarbinaryblob类型保存,无字符集和排序规则。

在很多方面_bin排序规则和binary排序规则不同。

10.1.7.6.1 比较单位

Binary string是字节流。比较是根据字节的值来比较。Nonbinary string是字符流,所以比较是以一个字符来进行比较。Nonbinary strings的排序规则定义了字符的顺序和比较。对于_bin排序规则就是以字符的二进制的值进行排序。

10.1.7.6.2 字符集转化

Nonbinary strings可以转化为其他字符集的字符串,就算是_bin排序规则的。但是对于binary strings就不会发生转化。

10.1.7.6.3 大小写转化

Nonbinary strings可以大小写转化:

mysql> SET NAMES latin1 COLLATE latin1_bin;

Query OK, 0 rows affected (0.02 sec)

 

mysql> SELECT LOWER('aA'), UPPER('zZ');

+-------------+-------------+

| LOWER('aA') | UPPER('zZ') |

+-------------+-------------+

| aa          | ZZ          |

+-------------+-------------+

1 row in set (0.13 sec)

binary strings中是不能转化的:

mysql> SET NAMES binary;

Query OK, 0 rows affected (0.00 sec)

 

mysql> SELECT LOWER('aA'), LOWER(CONVERT('aA' USING latin1));

+-------------+-----------------------------------+

| LOWER('aA') | LOWER(CONVERT('aA' USING latin1)) |

+-------------+-----------------------------------+

| aA          | aa                                |

+-------------+-----------------------------------+

1 row in set (0.00 sec)

10.1.7.6.4 比较时尾随的空格处理

Nonbinary strings所有的排序规则有PADSPACE行为。

mysql> SET NAMES utf8 COLLATE utf8_bin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> SELECT 'a ' = 'a';

+------------+

| 'a ' = 'a' |

+------------+

|          1 |

+------------+

1 row in set (0.00 sec)

但是binary string不行

mysql> SET NAMES binary;

Query OK, 0 rows affected (0.00 sec)

 

mysql> SELECT 'a ' = 'a';

+------------+

| 'a ' = 'a' |

+------------+

|          0 |

+------------+

1 row in set (0.00 sec)

10.1.7.6.5 插入和获取时的空格处理

CHAR(N)保存nonbinary strings数据,当插入长度不足N会以空格插入。

binary(N)保存binary string数据,会以0x00填充。

mysql> CREATE TABLE t1 (

    ->   a CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin,

    ->   b BINARY(10)

    -> );

Query OK, 0 rows affected (0.09 sec)

 

mysql> INSERT INTO t1 VALUES ('a','a');

Query OK, 1 row affected (0.01 sec)

 

mysql> SELECT HEX(a), HEX(b) FROM t1;

+--------+----------------------+

| HEX(a) | HEX(b)               |

+--------+----------------------+

| 61     | 61000000000000000000 |

+--------+----------------------+

1 row in set (0.04 sec)

10.1.7.7 BINARY操作

Binary操作是把它之后的字符串变为binary stringBINARY strcast(str as BINARY)的缩写。

10.1.7.8 Collation的影响例子

10.1.7.9 排序规则和INFORMATION_SCHEMA查找

INFORMATION_SCHEMA表的字符串列的排序规则是uft8_genernal_ci,大小写不敏感。但是文件系统的是否大小写敏感也会影响INFORMATION_SCHEMA的字符串列。如linux系统上大小写是敏感的。

linux 下:

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA

    -> WHERE SCHEMA_NAME = 'test';

+-------------+

| SCHEMA_NAME |

+-------------+

| test        |

+-------------+

1 row in set (0.01 sec)

 

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA

    -> WHERE SCHEMA_NAME = 'TEST';

Empty set (0.00 sec)

windows下:

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA

    -> WHERE SCHEMA_NAME = 'test';

+-------------+

| SCHEMA_NAME |

+-------------+

| test        |

+-------------+

1 row in set (0.00 sec)

 

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA

    -> WHERE SCHEMA_NAME = 'TEST';

+-------------+

| SCHEMA_NAME |

+-------------+

| TEST        |

+-------------+

1 row in set (0.00 sec)

如果列的排序规则和想到的不同,可以使用COLLATE语句显示指定一个排序规则:

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA

    -> WHERE SCHEMA_NAME COLLATE utf8_general_ci = 'test';

+-------------+

| SCHEMA_NAME |

+-------------+

| test        |

+-------------+

1 row in set (0.00 sec)

 

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA

    -> WHERE SCHEMA_NAME COLLATE utf8_general_ci = 'TEST';

| SCHEMA_NAME |

+-------------+

| test        |

+-------------+

1 row in set (0.00 sec)

当然也可以使用UPPER(),LOWER()

10.1.8 字符目录(String Repertoire)

字符集目录是字符集合。

字符串表达式有2种目录属性:

ASCII:只能包含unicode范围中U+0000U+007U

UNICODE:包含unicode范围从U+0000U+FFFF

ASCIIUNICODE的子集。

使用以下例子来说明目录:

·         字符串的内容限制了目录

SET NAMES utf8; SELECT 'abc';

SELECT _utf8'def';

SELECT N'MySQL';

尽管是使用了utf8字符集,但是只使用了ASCII范围的所以目录是ASCII。不是unicode

·         ascii字符集的列有ASCII目录。2个列concat的时候只能是子集连接超集,如:

CREATE TABLE t1 (

  c1 CHAR(1) CHARACTER SET latin1,

  c2 CHAR(1) CHARACTER SET ascii

);

INSERT INTO t1 VALUES ('a','b');

SELECT CONCAT(c1,c2) FROM t1;

ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT)

and (ascii_general_ci,IMPLICIT) for operation 'concat'

如果是子集连接超集。

+---------------+

| CONCAT(c1,c2) |

+---------------+

| ab            |

+---------------+

·         有一个字符串参数的函数,会集成参数的目录,如UPPER(_utf8’abc’)ASCII目录。

·         如果函数返回字符串,但是没有字符串参数,那么字符集使用chararter_set_connection作为结果,如果字符集是ascii那么目录使用ascii,否则使用unicode

·         当函数有2个或者2个以上字符参数时,使用最宽的目录,如unicodeascii宽,

CONCAT(_ucs2 0x0041, _ucs2 0x0042)

CONCAT(_ucs2 0x0041, _ucs2 0x00C2)

第一个函数都没超过ascii range,那么就是ascii,第二个超过了ascii,那么就是unicode

·         函数的返回字符串的目录由参数的目录决定

10.1.9 受字符集影响的操作

 

10.1.9.1 结果字符串

Mysql有很多操作和函数返回字符串。那么本节介绍结果字符串的排序规则和字符集。

对于简单的函数,输入字符串的,那么输出的字符串和输入的字符串的排序规则和字符集一样。

如果输入或者输出的字符串是binary串,那么是没有字符集和排序规则的,charset()collation()都是返回binary

如果有对个数据字符串,一个输出,那么有以下规则决定输出的排序规则:

·         如果现实的指定了collate x那么使用x

·         如果指定了collate x,collate y,报错

·         如果所有的排序规则是x,那么使用x

·         否则结果没有排序规则

10.1.9.2 CONVERT()CAST()

Convert提供了字符串在不同字符集上转化的功能

CONVERT(expr USING transcoding_name)

如:

SELECT CONVERT(_latin1'Müller' USING utf8);

INSERT INTO utf8table (utf8column)

    SELECT CONVERT(latin1field USING utf8) FROM latin1table;

也可以使用cast

CAST(character_string AS character_data_type CHARACTER SET charset_name)

如:

SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8);

Convertcast上不能使用字符集,可以再函数外面使用:

SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin;

10.1.9.3 SHOW语句和INFORMATION_SCHEMA

show命令的结果会提供一些字符集合排序规则信息,INFORMATION_SCHEMAshow命令类似。

10.1.10 Unicode支持

起初,mysql 4.1中就支持2中保存unicode的方式:

·         Ucs2:每个字节使用2个字节来编码。

·         Utf8:每个字节1-3个字节。

2个字符集支持来自unicode 3.0 BMP的字符。

如果超出BMP的字符会被替换为

mysql 5.6,为了要支持新的unicode的字符,所以增加了新的字符集。

Mysql 5.6支持的字符集:

Ucs2:2个字节来编码

Utf16:和ucs2类似,但是多了新增的字符

Utf16leutf16是大端的,utf16le是小端的

Utf32:每个字符3个字节

Utf8:每个字符1-3个字节

Utf8mb41-4个字节编码

Ucs2utf8支持BMP字符,utf8mb4,utf16,utf16leutf32支持BMP字符和额外字符。

10.1.10.1 ucs2字符集

ucs2字符集中,每个字符占2个字节。

10.1.10.2 utf16字符集

Utf16ucs2加上一些新增的字符,有以下 特性:

·         对于BMP字符,utf16ucs2编发都是一样的

·         对于增益的字符,如果大于0xffff,加上0xd800,放到前16bit,然后去之后的10bit加上0xdc00放到后16bit

10.1.10.3 utf16le字符集

utf16一样,只是是小端的。

10.1.10.4 utf32字符集

Utf32的字符长度是固定的,4个字节。在使用的时候要注意,定义的长度必须是4字节的倍数。

10.1.10.5 utf8字符集

Uft8是一种保存unicode的替代方案。

Utf8把字符以不同的字节长度进行编码:

·         基本的Latin字母,数字和标点符号使用一个字节。

·         大多数欧洲,中东字符使用2个字节。

·         韩文,中文,日文在3个或者4个字节中。

Utf8mysql5.6还是之前的版本都有2个特点:

·         不支持新增字符。

·         最多每个字符3个字节。

Uft8ucs2有相同的目录

10.1.10.6 utf8mb3字符集

在未来utf8会变成4字节的utf8,之前的3字节的utf8要被称为utf8mb8。为了避免将来复制之间的版本不同导致字符集问题,可以让用户指定utf8mb3字符集和排序规则。如:

CREATE TABLE t (s1 CHAR(1) CHARACTER SET utf8mb3;

SELECT * FROM t WHERE s1 COLLATE utf8mb3_general_ci = 'x';

DECLARE x VARCHAR(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_danish_ci;

SELECT CAST('a' AS CHAR CHARACTER SET utf8) COLLATE utf8_czech_ci;

这时,mysql会吧utf8mb3转化为utf8.目前utf8mb3只能用在CHARACTER SET子句上。

10.1.10.7 utf8mb4字符集

Utf8字符集一个字符最多3个字节,并且只包含BMP字符。Utf8mb4是最多使用每个字符4个字节:

·         对于BMP,utf8utf8mb4都是一样的存储特性,同样的code值,同样的编码,同样的长度。

·         对于新增的字符,utf8buneng baocun ,utf8mb4需要4个字节来保存。

Utf8mb4utf8的超集。

10.1.11 更新unicode的支持

对于utf8mb4utf8比,在相同的长度下utf8能够存更多的字符。可以使用alter tableutf8转成utf8mb4

CREATE TABLE t1 (

  col1 CHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,

  col2 CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL

) CHARACTER SET utf8;

ALTER TABLE t1

  DEFAULT CHARACTER SET utf8mb4,

  MODIFY col1 CHAR(10)

    CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,

  MODIFY col2 CHAR(10)

    CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;

对于BMP字符来说,utf8utf8mb4没有什么差别,但是utf8不支持新增字符。

对于所有的char,varchar,text需要注意:

·         检查所有的utf8定义的列,不会超过引擎的最大值

·         检查utf8的索引,不会超过引擎的最大值。

如果上面条件不满足,那么只能使用utf8.还有一些例子修改字符集可能要修改表结构:

·         Tinytext类型,只有255个字节,如果使用3个字节一个字符最多可以存85个,如果4字节那么最多63个。如果要使用utf8mb4又要超过63个字符,那么需要修改表结构。

·         Innodb索引,如果使用COMPACTREDUNDANT行格式,最长767个字节。对于utf8utf8mb4来说,最多可以保存255191个字符。也就是说,如果utf8长度超过191个字符,要转化为utf8mb4那么是不够的。

还有一些utf8utf8mb4的比较:

·         4字节的utf83字节的utf8性能差

·         Set names ‘utf8mb4’连接字符集会是4字节字符集,如果没有4个字节字符发送就没有问题。否则应用程序视图以最大3个字节获取信息可能会出问题。

·         应用不能发utf16,utf16le,utf32发送数据到老的服务

·         对于复制,如果master支持新增的字符集,那么slave必须也支持。

如果要从新系统换成老系统:

·         Ucs2utf8是没问题的

·         老系统不支持utf8mb4,utf16,utf16le,utf32

·         对于使用了utf8mb4的对象定义,可以先dump,然后把utf8mb4转为utf8

10.1.12 元数据为utf8

元数据需要满足以下特点:

·         所有的元数据要使用同一个字符集。

·         元数据必须包含所有语言的字符。

为了满足这个条件,mysql使用utf8字符集来存元数据。比如函数USER(),CURRENT_USER(),SESSION_USER(),SYSTEM_USER(),DATEBASE()VERSION(),默认都是使用utf8

使用character_set_system系统变量来控制元数据的字符集。

元数据是以character_set_system保存的,并不意味着DESCRIBE函数返回的字符集也是默认character_set_system字符集。如select column1 from t,从服务器返回给clientcolumn1的字符集是由character_set_results决定的,默认为latin1。如果想要把元数据的结果以其他字符集传输,可以使用set name 来设置。如果character_set_resultsnull,那么就不会发出转化,如果是元数据,那么会以character_Set_system字符集传输。

如果在表达式中使用user()那么mysql会自动进行传化。

SELECT * FROM t1 WHERE USER() = latin1_column;

INSERT INTO t1 (latin1_column) SELECT USER();

10.1.13 列字符集转化

使用alter tablebinary string或者nonbinary string 指定特定字符集。

·         如果列是bianry数据类型(BINARY,VARBINARY,BLOB)那么所有的值只能是一个字符集。

·         如果是nonbinary数据类型(char,varchar,text)包含的值,只能变编码为列自定的字符集。

假设表t是有一个col1定义为varbinary,使用一个字符集编码,并且为greek,那么可以通过以下语句去转化:

ALTER TABLE t MODIFY col1 VARCHAR(50) CHARACTER SET greek;

如果类型是binary(50),那么可以转化为char(50)但是,可能会出现0x00在末尾可能无法描述,需要使用trim()

UPDATE t SET col1 = TRIM(TRAILING 0x00 FROM col1);

如果表tcol1 char(50) character set latin1,要转化为utf8,可以直接:

ALTER TABLE t MODIFY col1 CHAR(50) CHARACTER SET utf8;

10.1.14 MySQL支持的字符集和排序规则

10.2 设置错误信息语言

默认mysql的错误信息是英语,但是可以指定其他语言。

mysql 5.6中错误信息在以下2个目录下去查找:

·         会从lc_messages_dirlc_messages 2个系统变量上去查找,使用如下:

shell> mysqld --lc_messages_dir=/usr/share/mysql --lc_messages=fr_FR

·         如果找不到,那么会放弃lc_messages,直接通过lc_messages_dir来查找。

lc_messages_dir是全局只读变量,lc_messages是全局和会话变量,可以运行时修改。默认语言文件在share/mysql/LANGUAGE目录下。

10.3 增加一个字符集

10.4 为字符集增加一个排序规则

10.5 配置字符集

10.6 MySQL时区的支持

mysql时区有好几个维护时区的设置:

·         系统级别的时区,当服务启动的时候,把系统使用的时区赋值给system_time_zone系统变量

·         服务当前时区,全局time_zone,表明当前所在的时区,初始值是’SYSTEM’表示和服务时区一样。全局服务时间可以在启动时设置,可以使用--default-time-zone

·         每个连接的时区,当连接的时候会话变量time_zone和全局time_zone一样,但是可以自己修改。

当前的时区,不会对UTC_TIMESTAMP()date,time,datetime列的值进行影响。当前的时区可以通过以下方式获取:

mysql> SELECT @@global.time_zone, @@session.time_zone;

时区的值可以是以下几种:

·           ‘SYSTEM’表示和服务的时区一样

·           ‘+10:00’,’-6:00’直接设置时区

·           使用命名的时区,如 ‘Europe/Helsinki’

mysql安装的时候会生成时区表在mysql数据库内,但是不会被load需要手动load

如果系统有自己的时区信息,那么可以使用mysql_tzinfo_to_sql程序来填充时区表,如linux,时区信息在/usr/shar/zoneinfo目录下。如果没有时区库,那么可以自己去下载。

mysql_tzinfo_to_sql用来把时区信息导入到时区表中:

shell> mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

10.6.1 跟上时区修改

当时区被修改的时候,应用程序还是使用老的时区,为了跟上,确定系统使用的时区信息是很有必要的。对于mysql2个方式可以用于跟上时区:

·           如果时区设置为‘system’那么操作系统的时区会影响mysql服务的事务。

·           如果实在/ect/localtime时区文件中替换,你就应该重启mysqld才能更新修改后的时区。

如果使用命名时区,那么要保证时区表中的数据时最新的。若系统有自己的时区信息,那么系统的时区信息被更新的时候,要保证mysql内的时区信息也要被更新。mysqldcache时区信息因此更新后需要重启服务。如果你要使用命名时区但是不确定有没有,那么先查看时区表。

mysql> SELECT COUNT(*) FROM mysql.time_zone_name;

+----------+

| COUNT(*) |

+----------+

|        0 |

+----------+

若不为空那么说明可以使用命名时区。

检查是否有夏令时规则:

SELECT CONVERT_TZ('2007-03-11 2:00:00','US/Eastern','US/Central');

SELECT CONVERT_TZ('2007-03-11 3:00:00','US/Eastern','US/Central');

更新前:

mysql> SELECT CONVERT_TZ('2007-03-11 2:00:00','US/Eastern','US/Central');

+------------------------------------------------------------+

| CONVERT_TZ('2007-03-11 2:00:00','US/Eastern','US/Central') |

+------------------------------------------------------------+

| 2007-03-11 01:00:00                                        |

+------------------------------------------------------------+

 

mysql> SELECT CONVERT_TZ('2007-03-11 3:00:00','US/Eastern','US/Central');

+------------------------------------------------------------+

| CONVERT_TZ('2007-03-11 3:00:00','US/Eastern','US/Central') |

+------------------------------------------------------------+

| 2007-03-11 02:00:00                                        |

+------------------------------------------------------------+

更新后:

mysql> SELECT CONVERT_TZ('2007-03-11 2:00:00','US/Eastern','US/Central');

+------------------------------------------------------------+

| CONVERT_TZ('2007-03-11 2:00:00','US/Eastern','US/Central') |

+------------------------------------------------------------+

| 2007-03-11 01:00:00                                        |

+------------------------------------------------------------+

 

mysql> SELECT CONVERT_TZ('2007-03-11 3:00:00','US/Eastern','US/Central');

+------------------------------------------------------------+

| CONVERT_TZ('2007-03-11 3:00:00','US/Eastern','US/Central') |

+------------------------------------------------------------+

| 2007-03-11 01:00:00                                        |

+------------------------------------------------------------+

 

10.6.2 时区闰秒支持(Time Zone Leap Seconad Support)

如果出现闰秒(23:59:60)情况会使用59:59

mysql> CREATE TABLE t1 (

    ->   a INT,

    ->   ts TIMESTAMP DEFAULT NOW(),

    ->   PRIMARY KEY (ts)

    -> );

Query OK, 0 rows affected (0.01 sec)

 

mysql> -- change to UTC

mysql> SET time_zone = '+00:00';

Query OK, 0 rows affected (0.00 sec)

 

mysql> -- Simulate NOW() = '2008-12-31 23:59:59'

mysql> SET timestamp = 1230767999;

Query OK, 0 rows affected (0.00 sec)

 

mysql> INSERT INTO t1 (a) VALUES (1);

Query OK, 1 row affected (0.00 sec)

 

mysql> -- Simulate NOW() = '2008-12-31 23:59:60'

mysql> SET timestamp = 1230768000;

Query OK, 0 rows affected (0.00 sec)

 

mysql> INSERT INTO t1 (a) VALUES (2);

Query OK, 1 row affected (0.00 sec)

 

mysql> -- values differ internally but display the same

mysql> SELECT a, ts, UNIX_TIMESTAMP(ts) FROM t1;

+------+---------------------+--------------------+

| a    | ts                  | UNIX_TIMESTAMP(ts) |

+------+---------------------+--------------------+

|    1 | 2008-12-31 23:59:59 |         1230767999 |

|    2 | 2008-12-31 23:59:59 |         1230768000 |

+------+---------------------+--------------------+

2 rows in set (0.00 sec)

 

mysql> -- only the non-leap value matches

mysql> SELECT * FROM t1 WHERE ts = '2008-12-31 23:59:59';

+------+---------------------+

| a    | ts                  

+------+---------------------+

|    1 | 2008-12-31 23:59:59 |

+------+---------------------+

1 row in set (0.00 sec)

 

mysql> -- the leap value with seconds=60 is invalid

mysql> SELECT * FROM t1 WHERE ts = '2008-12-31 23:59:60';

Empty set, 2 warnings (0.00 sec)

10.7 MySQL Server本地化支持

lc_time_names控制了日期,月份星期的显示语言,会影响DATA_FORMAT()DAYNAME()MONTHNAME()的输出。但是lc_time_names不影响STR_TO_DATE()GET_FORMAT()format虽然不受lc_time_names影响,但是通过传入参数可以影响结果。

mysql> SET NAMES 'utf8';

Query OK, 0 rows affected (0.09 sec)

 

mysql> SELECT @@lc_time_names;

+-----------------+

| @@lc_time_names |

+-----------------+

| en_US           |

+-----------------+

1 row in set (0.00 sec)

 

mysql> SELECT DAYNAME('2010-01-01'), MONTHNAME('2010-01-01');

+-----------------------+-------------------------+

| DAYNAME('2010-01-01') | MONTHNAME('2010-01-01') |

+-----------------------+-------------------------+

| Friday                | January                 |

+-----------------------+-------------------------+

1 row in set (0.00 sec)

 

mysql> SELECT DATE_FORMAT('2010-01-01','%W %a %M %b');

+-----------------------------------------+

| DATE_FORMAT('2010-01-01','%W %a %M %b') |

+-----------------------------------------+

| Friday Fri January Jan                  |

+-----------------------------------------+

1 row in set (0.00 sec)

 

mysql> SET lc_time_names = 'es_MX';

Query OK, 0 rows affected (0.00 sec)

 

mysql> SELECT @@lc_time_names;

+-----------------+

| @@lc_time_names |

+-----------------+

| es_MX           |

+-----------------+

1 row in set (0.00 sec)

 

mysql>

 

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
7天前
|
关系型数据库 MySQL
Mysql基础第三十天,全球化和本地化
Mysql基础第三十天,全球化和本地化
26 0
Mysql基础第三十天,全球化和本地化
|
7天前
|
SQL 数据采集 关系型数据库
如何解决MySQL报错 You have an error in your SQL syntax; check the manual that corresponds to your MySQL?
如何解决MySQL报错 You have an error in your SQL syntax; check the manual that corresponds to your MySQL?
|
7天前
|
SQL 关系型数据库 MySQL
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
33 0
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
|
7月前
|
SQL 关系型数据库 MySQL
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
错误提示: Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; che
92 0
|
9月前
|
SQL 关系型数据库 MySQL
check the manual that corresponds to your MySQL server version for the right syntax to use near lin
注意这种一般情况下是语法问题,说明mysql的各种语句还是不够熟练, (1)看表名是不是使用了Mysql的关键字,如果是,请立马改掉; (2)语句给出哪里错误,如我的题目中给出“near”,说明在那附近有问题。 (3)检查mybatis中mapper里sql语句占位符是否写对,我的问题是将#{name}写成了#(name),尴尬。
135 0
|
11月前
|
SQL 关系型数据库 MySQL
修改mysql的密码时遇到问题ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corre
修改mysql的密码时遇到问题ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corre
|
SQL 并行计算 关系型数据库
Citus 分布式 PostgreSQL 集群 - SQL Reference(SQL支持和变通方案)
Citus 分布式 PostgreSQL 集群 - SQL Reference(SQL支持和变通方案)
150 0
|
SQL 缓存 关系型数据库
Citus 分布式 PostgreSQL 集群 - SQL Reference(手动查询传播)
Citus 分布式 PostgreSQL 集群 - SQL Reference(手动查询传播)
159 0
|
SQL 存储 关系型数据库
Citus 分布式 PostgreSQL 集群 - SQL Reference(查询处理)
Citus 分布式 PostgreSQL 集群 - SQL Reference(查询处理)
154 0
|
存储 SQL JSON
Citus 分布式 PostgreSQL 集群 - SQL Reference(查询分布式表 SQL)
Citus 分布式 PostgreSQL 集群 - SQL Reference(查询分布式表 SQL)
180 0