本文关键字：字符集、建库建表
一、数据库和字符集
1. 建库时指定
mysql> create database ytt_new2 default character set latin1 collate latin1_bin;Query OK, 1 row affected (0.03 sec)mysql> use ytt_new2Database changedmysql> select @, @;+--------------------------+----------------------+| @ | @ |+--------------------------+----------------------+| latin1 | latin1_bin |+--------------------------+----------------------+1 row in set (0.00 sec)
2. 改库时指定
改库类似建库，效果一样。但需要注意的一点是，修改库字符集与排序规则后，之前基于这个库创建的各种对象，还是沿用老的字符集与排序规则。
举个例子，对存储过程的影响：
-- 简单写个存储过程DELIMITER $$USE `ytt_new2`$$DROP PROCEDURE IF EXISTS `sp_demo`$$CREATE DEFINER=`root`@`%` PROCEDURE `sp_demo`(IN f1 VARCHAR(10),IN f2 VARCHAR(10))BEGINDECLARE v1 VARCHAR(20);SET v1 = CONCAT(f1,f2);SELECT v1 AS result;END$$DELIMITER ;
mysql> show create procedure sp_demo\G*************************** 1. row ***************************Procedure: sp_demo...Database Collation: latin1_bin1 row in set (0.00 sec)-- 那接下来改掉数据库的字符集为 UTF8mysql> alter database ytt_new2 character set utf8 collate utf8_general_ci;Query OK, 1 row affected, 2 warning (0.02 sec)mysql> select @@character_set_database, @@collation_database;+--------------------------+----------------------+| @@character_set_database | @@collation_database |+--------------------------+----------------------+| utf8 | utf8_general_ci |+--------------------------+----------------------+1 row in set (0.00 sec)-- 再查看存储过程 sp_demo 的字符集，还是之前的。mysql> show create procedure sp_demo\G*************************** 1. row ***************************Procedure: sp_demo...Database Collation: latin1_bin1 row in set (0.00 sec)-- 此时，调用存储过程，字符集不对，报编码错误。mysql> call sp_demo('我','你');ERROR 1366 (HY000): Incorrect string value: '\xE6\x88\x91' for column 'f1' at row 1-- 改变存储过程字符集只能删除重建，重新执行下之前的存储过程代码，再次调用，结果就正常了。mysql> call sp_demo('我','你');+--------+| result |+--------+| 我你 |+--------+1 row in set (0.00 sec)Query OK, 0 rows affected, 2 warnings (0.01 sec)
3. 参数的指定
mysql> select @@character_set_server charset, @@collation_server collation-> union all-> select @@character_set_database, @@collation_database;+---------+--------------------+| charset | collation |+---------+--------------------+| utf8mb4 | utf8mb4_0900_ai_ci || utf8mb4 | utf8mb4_0900_ai_ci |+---------+--------------------+2 rows in set (0.00 sec)
mysql> show create database ytt_new3\G*************************** 1. row ***************************Database: ytt_new3Create Database: CREATE DATABASE `ytt_new3` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */1 row in set (0.00 sec)
二、表和字符集
1. 建表时指定（显式设置）
-- 创建新库 ytt_new4mysql> create database ytt_new4;Query OK, 1 row affected (0.02 sec)mysql> use ytt_new4;Database changed-- 创建新表 t1, 字符集 latin1, 排序规则 latin1_binmysql> create table t1(a1 int) charset latin1 collate latin1_bin;Query OK, 0 rows affected (0.05 sec)mysql> show create table t1\G*************************** 1. row ***************************Table: t1Create Table: CREATE TABLE `t1` (`a1` int DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin1 row in set (0.00 sec)
2. 继承设置（隐式转换）
-- 当前数据库 ytt_new4.mysql> use ytt_new4;Database changed-- 表 t2 属于当前数据库 ytt_new4mysql> create table t2(a1 int);Query OK, 0 rows affected (0.05 sec)-- 查看表 t2 字符集和排序规则mysql> show create table t2\G*************************** 1. row ***************************Table: t2Create Table: CREATE TABLE `t2` (`a1` int DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)-- 创建表 t3，不过属于数据库 ytt_new5mysql> create database ytt_new5 character set gbk;Query OK, 1 row affected (0.03 sec)mysql> create table ytt_new5.t3 (id int);Query OK, 0 rows affected (0.04 sec)-- 查看表 t3 字符集，和数据库 ytt_new5 一致mysql> show create table ytt_new5.t3\G*************************** 1. row ***************************Table: t3Create Table: CREATE TABLE `t3` (`id` int DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=gbk1 row in set (0.01 sec)
3. 视图
视图其实就是虚拟的表，所以视图的字符集也划在表这块简单介绍下。视图的字符集完全依赖客户端的字符集设置。
mysql> set names gbk;Query OK, 0 rows affected (0.00 sec)-- 此时视图 v_t3 编码为 gbkmysql> create view v_t3 as select * from t3;Query OK, 0 rows affected (0.01 sec)mysql> set names gb18030;Query OK, 0 rows affected (0.00 sec)-- 此时视图 v_t31 编码为 gb18030mysql> create view v_t3_1 as select * from t3;Query OK, 0 rows affected (0.01 sec)-- 查看这两个视图的编码mysql> show create view v_t3\G*************************** 1. row ***************************View: v_t3Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_t3` AS select `t3`.`a1` AS `a1` from `t3`character_set_client: gbkcollation_connection: gbk_chinese_ci1 row in set (0.01 sec)mysql> show create view v_t3_1\G*************************** 1. row ***************************View: v_t3_1Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_t3_1` AS select `t3`.`a1` AS `a1` from `t3`character_set_client: gb18030collation_connection: gb18030_chinese_ci1 row in set (0.00 sec)
4. 触发器
触发器基于表，所以触发器也归类到表这块。其实触发器的编码规则和视图一样。也是依赖客户端的设定。
-- 客户端编码为 utf8mysql> set names utf8;Query OK, 0 rows affected, 1 warning (0.00 sec)-- 触发器的编码也继承同样的客户端编码mysql> create trigger tr_after_insert_t3 after insert on t3 for each row insert into t4 values(new.a1);Query OK, 0 rows affected (0.01 sec)mysql> show create trigger tr_after_insert_t3\G*************************** 1. row ***************************Trigger: tr_after_insert_t3sql_mode:SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER `tr_after_insert_t3` AFTER INSERT ON `t3` FOR EACH ROW insert into t4 values(new.a1)character_set_client: utf8collation_connection: utf8_general_ciDatabase Collation: utf8mb4_0900_ai_ciCreated: 2020-03-09 11:32:23.941 row in set (0.00 sec)
三、列和字符集
1. 显式指定
-- 创建新库 ytt_new6mysql> create database ytt_new6 character set latin1;Query OK, 1 row affected (0.02 sec)mysql> use ytt_new6;Database changed-- 在 ytt_new6 下创建表 t1，拥有字段 a1,a2,a3 分别给定不同的字符集和排序规则mysql> create table t1( a1 char(10) charset latin1 collate latin1_bin,a2 char(10) charset gbk collate gbk_bin,a3 char(10) charset utf8mb4 collate utf8mb4_bin);Query OK, 0 rows affected (0.05 sec)mysql> show create table t1\G*************************** 1. row ***************************Table: t1Create Table: CREATE TABLE `t1` (`a1` char(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,`a2` char(10) CHARACTER SET gbk COLLATE gbk_bin DEFAULT NULL,`a3` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)
-- 没有显式指定每列字符集，报错mysql> insert into t1 values ('character ','字符集合','字符集合');ERROR 1366 (HY000): Incorrect string value: '\xAC\xA6\xE9\x9B\x86\xE5...' for column 'a2' at row 1-- 为每列显式添加 Introducermysql> insert into t1 values (_latin1 'character ',_gbk '字符集合',_utf8mb4 '字符集合');Query OK, 1 row affected (0.01 sec)
SELECT * 拿出所有记录，结果发现有一个字段据显示不正常。mysql> select * from t1;+-----------+------------------+--------------+| a1 | a2 | a3 |+-----------+------------------+--------------+| character | 瀛楃?闆嗗悎 | 字符集合 |+-----------+------------------+--------------+1 row in set (0.00 sec)
-- 以字符集 GBK 输出列 a1 a2，由于 a1 是保存的是字母，所以兼容输出。mysql> set names gbk;Query OK, 0 rows affected (0.00 sec)mysql> select a1,a2 from t1;+-----------+--------------+| a1 | a2 |+-----------+--------------+| character | 字符集合 |+-----------+--------------+1 row in set (0.00 sec)-- 以字符集utf8mb4和gbk不兼容，得单独输出列a3。或者单独输出a2.mysql> set names utf8mb4;Query OK, 0 rows affected (0.00 sec)mysql> select a3 from t1;+--------------+| a3 |+--------------+| 字符集合 |+--------------+1 row in set (0.00 sec)
2. 隐式转换
-- 建表 t2，指定字符集为 gbk.mysql> create table t2(a1 varchar(10),a2 varchar(10)) charset gbk;Query OK, 0 rows affected (0.05 sec)mysql> show create table t2\G*************************** 1. row ***************************Table: t2Create Table: CREATE TABLE `t2` (`a1` varchar(10) DEFAULT NULL,`a2` varchar(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=gbk1 row in set (0.01 sec)
mysql> alter table t2 charset utf8;Query OK, 0 rows affected, 1 warning (0.01 sec)Records: 0 Duplicates: 0 Warnings: 1mysql> show create table t2\G*************************** 1. row ***************************Table: t2Create Table: CREATE TABLE `t2` (`a1` varchar(10) CHARACTER SET gbk DEFAULT NULL,`a2` varchar(10) CHARACTER SET gbk DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)
mysql> alter table t2 convert to character set utf8;Query OK, 0 rows affected, 1 warning (0.09 sec)Records: 0 Duplicates: 0 Warnings: 1mysql> show create table t2\G*************************** 1. row ***************************Table: t2Create Table: CREATE TABLE `t2` (`a1` varchar(10) DEFAULT NULL,`a2` varchar(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)
大致总结下，这篇我详细介绍了字符集在 MySQL 数据库，表以及列相关对象处理时的注意事项，并且举例说明。希望对大家有帮助。
关于 MySQL 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！
