character sets and collations in mysql



*************************************************** 转发请注明原文，尊重原创** 原文来自： blog.csdn.net/clark_xu 徐长亮的专栏*************************************************

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