/********************************
--1 character sets and collations in mysql
********************************/
--[1]
--a.列出可用的字符集
select * from information_schema.CHARACTER_SETS;
show CHARACTER set;
====
CHARACTER_SET_NAME DEFAULT_COLLATE_NAME DESCRIPTION MAXLEN
gb2312 gb2312_chinese_ci GB2312 Simplified Chinese 2
----------------------------------------------------------
--[2]
--a.COLLATIONS表:校准 提供了关于各字符集的对照信息
select *
from information_schema.COLLATIONS;
====
CHARACTER_SET_NAME COLLATION_NAME ID IS_DEFAULT IS_COMPILED SORTLEN
utf8 utf8_general_ci 33 Yes Yes 1
select *
from information_schema.COLLATIONS
where CHARACTER_SET_NAME='utf8'
order by COLLATION_NAME;
/********************************
--2 set character sets and collations in mysql
********************************/
-----------------------------------
--[1] server charcter set and collation
-----------------------------------
--(1)
mysqld --character-set-server=utf8
--collation-server=utf8_unicode_520_ci
--a.注:
utf8_unicode_520_ci是基于usc 5.2.0 weight keys
--(2) cmak_CHARSET=UFT
cmak . -DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_unicode_520_ci
-----------------------------------
--[2] DATABASE charcter set and collation
-----------------------------------
--(1)
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
关键字schema可以替换database
--(2)
数据库文件db.opt保存了相关字符集信息:
default-character-set=utf8
default-collation=utf8_general_ci
--b.查看数据库字符集2
select @@character_set_database,
@@collation_database;
--c.
select default_character_set_name,default_collation_name
from information_schema.SCHEMATA
where schema_name='test_ldm'
-----------------------------------
--[3] table charcter set and collation
-----------------------------------
--(1)创建表
create table tbl_name
default character set charset_name
collate collation_name
alter table tbl_name
default character set charset_name
collate collation_name
-----------------------------------
--[4] table charcter set and collation
-----------------------------------
--(1)column_type:char,varchar,text
col_name {char|varchar|text} (col_length)
character set charset_name
collate collation_name
col_name {emnum|set} (val_list)
character set charset_name
collate collation_name
--(2) create table ,alter table modify col
create table t1
(
col_name {char|varchar|text} (col_length)
character set charset_name
collate collation_name
)
alter table t1 modify
col_name {char|varchar|text} (col_length)
character set charset_name
collate collation_name
-----------------------------------
--[5] character String literal charcter set and collation
-----------------------------------
--(1)语法
--a.
[_charset_name]'string' [collate collation_name]
--b.
select _latin1'string' collate latin1_danish_ci;
select 'string'
select _latin1'string';
-----------------------------------
--[6] National charcter set and collation
-----------------------------------
NCHAR 或者NATIONAL CHAR 表示列名应该被指定某种字符集;mysql 使用 utf8作为预定义的字符集
使用N或者n来使用国家字符集来创建字符串,例如
select N'some text';
/********************************
--3 客户端连接的character sets and collations
********************************/
-----------------------------------
--[1] National charcter set and collation
-----------------------------------
--(1)server charcter set and collation影响
系统变量character_set_server,collation_server指定
select @@character_set_server,
@@collation_server;
--(2)DATABASE charcter set and collation影响
select @@character_set_database,
@@collation_database;
--(3)原理:
client发送sql语句;
server发送结果集,错误信息;
--(4)
--a.当查询离开客户端时是什么字符集
服务器通过character_set_client参数来解析client的sql语句
--b.服务器在接收到它后应将一个语句转换成什么字符集
将client的sql语句从character_set_client字符集
转换为character_set_connection(collation_connection)
select @@character_set_client,
@@character_set_connection,
@@collation_connection;
--c.在结果集由服务器传递给客户端之前,需要转换成什么字符集
character_set_results
--(5)
set names 'charset_name'
等价于
set character_set_client=charset_name
set character_set_connection=charset_name
set collation_connection=charset_name