宜昌网站建设开发团队,扬州网站建设费用,网站上那些轮播图视频怎么做的,贵州网站建设seo优化本文关键字#xff1a;字符集、建库建表一、数据库和字符集1. 建库时指定创建数据库时#xff0c;显式指定字符集和排序规则#xff0c;同时#xff0c;当切换到当前数据库后#xff0c;参数 character_set_database#xff0c;collation_database 分别被覆盖为当前显式指…本文关键字字符集、建库建表一、数据库和字符集1. 建库时指定创建数据库时显式指定字符集和排序规则同时当切换到当前数据库后参数 character_set_databasecollation_database 分别被覆盖为当前显式指定的字符集和排序规则。举个简单例子创建数据库 ytt_new2显式指定字符集为 latin1同时排序规则为 latin1_bin。之后切换到数据库 ytt_new2 后对应的系统参数也被修改。mysql create database ytt_new2 default character set latin1 collate latin1_bin;Query OK, 1 row affected (0.03 sec)mysql use ytt_new2Database changedmysqlselect character_set_database, collation_database;------------------------------------------------| character_set_database | collation_database |------------------------------------------------| latin1 | latin1_bin |------------------------------------------------1 row in set (0.00 sec)2. 改库时指定改库类似建库效果一样。但需要注意的一点是修改库字符集与排序规则后之前基于这个库创建的各种对象还是沿用老的字符集与排序规则。举个例子对存储过程的影响-- 简单写个存储过程DELIMITER $$USE ytt_new2$$DROP PROCEDURE IF EXISTS sp_demo$$CREATE DEFINERroot% PROCEDURE sp_demo( IN f1 VARCHAR(10), IN f2 VARCHAR(10) )BEGIN DECLARE v1 VARCHAR(20); SET v1 CONCAT(f1,f2); SELECT v1 AS result; END$$DELIMITER ;查看这个存储过程的字符集这里看到排序规则是 latin1_bin对应的字符集是 latin1和数据库 ytt_new2 一致。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. 参数的指定参数 character_set_database 和 collation_database 如果没有指定默认继承服务器端对应参数 character_set_server 和 collation_server。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 ENCRYPTIONN */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.05sec)mysql show create table t1\G*************************** 1. row *************************** Table: t1Create Table: CREATE TABLE t1 ( a1 int DEFAULT NULL) ENGINEInnoDB DEFAULT CHARSETlatin1 COLLATElatin1_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) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_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) ENGINEInnoDB DEFAULT CHARSETgbk1 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_t3 Create View: CREATE ALGORITHMUNDEFINED DEFINERrootlocalhost SQL SECURITY DEFINER VIEW v_t3 AS select t3.a1 AS a1 from t3character_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_1 Create View: CREATE ALGORITHMUNDEFINED DEFINERrootlocalhost SQL SECURITY DEFINER VIEW v_t3_1 AS select t3.a1 AS a1 from t3character_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_t3 sql_mode:SQL Original Statement: CREATE DEFINERrootlocalhost TRIGGER tr_after_insert_t3 AFTER INSERT ON t3 FOR EACH ROW insert into t4 values(new.a1)character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8mb4_0900_ai_ci Created: 2020-03-09 11:32:23.941 row in set (0.00 sec)三、列和字符集1. 显式指定同数据库和表一样列也可以指定特定的字符集和排序规则。虽说是可以这样做但是非常不推荐最主要原因是每个列字符集不一样导致写入和检索都得额外的编写 SQL。-- 创建新库 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) ENGINEInnoDB DEFAULT CHARSETlatin11 row in set (0.00 sec)那接下来简单插入一条记录需要为每列添加 introduer否则报错。-- 没有显式指定每列字符集报错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) ENGINEInnoDB DEFAULT CHARSETgbk1 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) ENGINEInnoDB DEFAULT CHARSETutf81 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) ENGINEInnoDB DEFAULT CHARSETutf81 row in set (0.00 sec)那到这儿我们已经了解了字符集对数据库表以及列的使用以及相关影响。大致总结下这篇我详细介绍了字符集在 MySQL 数据库表以及列相关对象处理时的注意事项并且举例说明。希望对大家有帮助。
