當(dāng)前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

mysql insert 字符集_有关 MySQL 字符集的注意事项-爱可生

發(fā)布時(shí)間：2023/12/3 数据库 31 豆豆

生活随笔收集整理的這篇文章主要介紹了 mysql insert 字符集_有关 MySQL 字符集的注意事项-爱可生小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

本文關(guān)鍵字：字符集、建庫建表

一、數(shù)據(jù)庫和字符集

1. 建庫時(shí)指定

創(chuàng)建數(shù)據(jù)庫時(shí)，顯式指定字符集和排序規(guī)則，同時(shí)，當(dāng)切換到當(dāng)前數(shù)據(jù)庫后，參數(shù) character_set_database，collation_database 分別被覆蓋為當(dāng)前顯式指定的字符集和排序規(guī)則。舉個(gè)簡(jiǎn)單例子，創(chuàng)建數(shù)據(jù)庫 ytt_new2，顯式指定字符集為 latin1，同時(shí)排序規(guī)則為 latin1_bin。之后切換到數(shù)據(jù)庫 ytt_new2 后，對(duì)應(yīng)的系統(tǒng)參數(shù)也被修改。

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 @@character_set_database, @@collation_database;+--------------------------+----------------------+| @@character_set_database | @@collation_database |+--------------------------+----------------------+| latin1 | latin1_bin |+--------------------------+----------------------+1 row in set (0.00 sec)

2. 改庫時(shí)指定

改庫類似建庫，效果一樣。但需要注意的一點(diǎn)是，修改庫字符集與排序規(guī)則后，之前基于這個(gè)庫創(chuàng)建的各種對(duì)象，還是沿用老的字符集與排序規(guī)則。

舉個(gè)例子，對(duì)存儲(chǔ)過程的影響：

-- 簡(jiǎn)單寫個(gè)存儲(chǔ)過程DELIMITER $$USE `ytt_new2`$$DROP PROCEDURE IF EXISTS `sp_demo`$$CREATE DEFINER=`root`@`%` 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 ;

查看這個(gè)存儲(chǔ)過程的字符集，這里看到排序規(guī)則是 latin1_bin，對(duì)應(yīng)的字符集是 latin1，和數(shù)據(jù)庫 ytt_new2 一致。

mysql> show create procedure sp_demo\G*************************** 1. row *************************** Procedure: sp_demo... Database Collation: latin1_bin1 row in set (0.00 sec)-- 那接下來改掉數(shù)據(jù)庫的字符集為 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)-- 再查看存儲(chǔ)過程 sp_demo 的字符集，還是之前的。

mysql> show create procedure sp_demo\G*************************** 1. row *************************** Procedure: sp_demo... Database Collation: latin1_bin1 row in set (0.00 sec)-- 此時(shí)，調(diào)用存儲(chǔ)過程，字符集不對(duì)，報(bào)編碼錯(cuò)誤。

mysql> call sp_demo('我','你');ERROR 1366 (HY000): Incorrect string value: '\xE6\x88\x91' for column 'f1' at row 1-- 改變存儲(chǔ)過程字符集只能刪除重建，重新執(zhí)行下之前的存儲(chǔ)過程代碼，再次調(diào)用，結(jié)果就正常了。

mysql> call sp_demo('我','你');+--------+| result |+--------+| 我你 |+--------+1 row in set (0.00 sec)Query OK, 0 rows affected, 2 warnings (0.01 sec)

3. 參數(shù)的指定

參數(shù) character_set_database 和 collation_database 如果沒有指定，默認(rèn)繼承服務(wù)器端對(duì)應(yīng)參數(shù) 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)

那這種情況下，建庫或者改庫時(shí)不指定具體的字符集和排序規(guī)則，默認(rèn)繼承這兩個(gè)參數(shù)：

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. 建表時(shí)指定(顯式設(shè)置)

同建庫一樣，顯式指定字符集和排序規(guī)則，優(yōu)先級(jí)最高，以指定的值為準(zhǔn)。

-- 創(chuàng)建新庫 ytt_new4mysql> create database ytt_new4;Query OK, 1 row affected (0.02 sec)mysql> use ytt_new4;Database changed-- 創(chuàng)建新表 t1, 字符集 latin1, 排序規(guī)則 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. 繼承設(shè)置(隱式轉(zhuǎn)換)

默認(rèn)繼承所屬數(shù)據(jù)庫級(jí)別的字符集和排序規(guī)則。這里需要注意的是所屬數(shù)據(jù)庫，不是當(dāng)前數(shù)據(jù)庫。

-- 當(dāng)前數(shù)據(jù)庫 ytt_new4.mysql> use ytt_new4;Database changed-- 表 t2 屬于當(dāng)前數(shù)據(jù)庫 ytt_new4mysql> create table t2(a1 int);Query OK, 0 rows affected (0.05 sec)-- 查看表 t2 字符集和排序規(guī)則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)--

創(chuàng)建表 t3，不過屬于數(shù)據(jù)庫 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 字符集，和數(shù)據(jù)庫 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. 視圖

視圖其實(shí)就是虛擬的表，所以視圖的字符集也劃在表這塊簡(jiǎn)單介紹下。視圖的字符集完全依賴客戶端的字符集設(shè)置。

比如：

mysql> set names gbk;Query OK, 0 rows affected (0.00 sec)-- 此時(shí)視圖 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)--

此時(shí)視圖 v_t31 編碼為 gb18030mysql> create view v_t3_1 as select * from t3;Query OK, 0 rows affected (0.01 sec)--

查看這兩個(gè)視圖的編碼mysql> show create view v_t3\G*************************** 1. row *************************** View: v_t3 Create 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_1 Create 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. 觸發(fā)器

觸發(fā)器基于表，所以觸發(fā)器也歸類到表這塊。其實(shí)觸發(fā)器的編碼規(guī)則和視圖一樣。也是依賴客戶端的設(shè)定。

比如一個(gè)簡(jiǎn)單的觸發(fā)器：

-- 客戶端編碼為 utf8mysql> set names utf8;Query OK, 0 rows affected, 1 warning (0.00 sec)-- 觸發(fā)器的編碼也繼承同樣的客戶端編碼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 DEFINER=`root`@`localhost` 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. 顯式指定

同數(shù)據(jù)庫和表一樣，列也可以指定特定的字符集和排序規(guī)則。雖說是可以這樣做，但是非常不推薦，最主要原因是每個(gè)列字符集不一樣，導(dǎo)致寫入和檢索都得額外的編寫 SQL。

-- 創(chuàng)建新庫 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 下創(chuàng)建表 t1，擁有字段 a1,a2,a3 分別給定不同的字符集和排序規(guī)則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)

那接下來簡(jiǎn)單插入一條記錄，需要為每列添加 introduer，否則報(bào)錯(cuò)。

-- 沒有顯式指定每列字符集，報(bào)錯(cuò)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)

查詢出來最麻煩，因?yàn)槊看尾樵兂鰜淼慕Y(jié)果只能有一個(gè)字符集，字符集兼容的列可以一起檢索；不兼容的列得分開檢索。舉個(gè)例子，我想簡(jiǎn)單的 SELECT * 拿出所有記錄，結(jié)果發(fā)現(xiàn)有一個(gè)字段據(jù)顯示不正常。

mysql> select * from t1;+-----------+------------------+--------------+| a1 | a2 | a3 |+-----------+------------------+--------------+| character | 瀛?闆 | 字符集合 |+-----------+------------------+--------------+1 row in set (0.00 sec)

所以針對(duì)這種情形，該怎么檢索數(shù)據(jù)呢？必須得對(duì)單個(gè)字段檢索或者是對(duì)兼容的列一起檢索。

-- 以字符集 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不兼容，得單獨(dú)輸出列a3?；蛘邌为?dú)輸出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. 隱式轉(zhuǎn)換

這種方式，是最推薦的，也是最長(zhǎng)的方式，所有列繼承表的字符集，不單獨(dú)指定。

-- 建表 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)

但是這里有一個(gè)需要注意的點(diǎn)，如果此時(shí)對(duì)表進(jìn)行字符集變更，那表的列依然保留原來的字符集。

例如：

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)

如果想把表里字段的字符集也改了，應(yīng)該用以下語句更改表字符集。

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)

那到這兒，我們已經(jīng)了解了字符集對(duì)數(shù)據(jù)庫，表以及列的使用以及相關(guān)影響。

大致總結(jié)下，這篇我詳細(xì)介紹了字符集在 MySQL 數(shù)據(jù)庫，表以及列相關(guān)對(duì)象處理時(shí)的注意事項(xiàng)，并且舉例說明。希望對(duì)大家有幫助。

總結(jié)

以上是生活随笔為你收集整理的mysql insert 字符集_有关 MySQL 字符集的注意事项-爱可生的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。