您好,登錄后才能下訂單哦!
總結(jié)
1、create database時會自動生成一個文件db.opt,存放的數(shù)據(jù)庫的默認(rèn)字符集,show create database時顯示數(shù)據(jù)庫默認(rèn)字符集即db.opt中字符集
2、這個文件丟失不影響數(shù)據(jù)庫運(yùn)行,該文件丟失之后新建表時,找不到數(shù)據(jù)庫的默認(rèn)字符集,就把character_set_server當(dāng)成數(shù)據(jù)庫的默認(rèn)字符集,show create database時顯示character_set_server字符集
mysql> show variables like 'character_set_server';
+----------------------+--------+
| Variable_name | Value |
+----------------------+--------+
| character_set_server | latin1 |
+----------------------+--------+
test1庫沒有指定字符集,使用character_set_server值
mysql> create database test1;
[root@mydb test1]# cat /var/lib/mysql/test1/db.opt
default-character-set=latin1
default-collation=latin1_swedish_ci
test2庫指定了字符集utf16
mysql> create database test2 character set=utf16;
[root@mydb test1]# cat /var/lib/mysql/test2/db.opt
default-character-set=utf16
default-collation=utf16_general_ci
test1庫的默認(rèn)字符集latin1,show create database顯示默認(rèn)字符集latin1
tab1表使用數(shù)據(jù)庫默認(rèn)字符集latin1
mysql> show create database test1;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| test1 | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
mysql> create table test1.tab1 (hid int);
test1庫的沒有了默認(rèn)字符集,因?yàn)閐b.opt文件不存在了,show create database顯示character_set_server字符集latin1
tab2表沒有辦法使用數(shù)據(jù)庫默認(rèn)字符集,使用character_set_server字符集latin1
[root@mydb test1]# mv db.opt db.opt20181015
[root@mydb test1]# service mysqld restart
mysql> show create database test1;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| test1 | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
mysql> create table test1.tab2 (hid int);
test1庫的沒有了默認(rèn)字符集,因?yàn)閐b.opt文件不存在了,show create database顯示character_set_server字符集latin7
tab2表沒有辦法使用數(shù)據(jù)庫默認(rèn)字符集,使用character_set_server字符集latin7
[root@mydb test1]# vi /etc/my.cnf
[mysqld]
character_set_server=latin7
[root@mydb test1]# service mysqld restart
mysql> show create database test1;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| test1 | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET latin7 */ |
+----------+------------------------------------------------------------------+
mysql> create table test1.tab3 (hid int);
test1庫的默認(rèn)字符集latin1,show create database顯示默認(rèn)字符集latin1
tab4表使用數(shù)據(jù)庫默認(rèn)字符集latin1
[root@mydb test1]# mv db.opt20181015 db.opt
[root@mydb test1]# service mysqld restart
mysql> show create database test1;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| test1 | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
mysql> create table test1.tab4(hid int);
tab1表使用數(shù)據(jù)庫默認(rèn)字符集latin1
mysql> show create table test1.tab1;
+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------+
| tab1 | CREATE TABLE `tab1` (
`hid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------+
tab2表使用character_set_server字符集latin1
mysql> show create table test1.tab2;
+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------+
| tab2 | CREATE TABLE `tab2` (
`hid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------+
tab3表使用character_set_server字符集latin7
mysql> show create table test1.tab3;
+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------+
| tab3 | CREATE TABLE `tab3` (
`hid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin7 |
+-------+-------------------------------------------------------------------------------------------+
tab4表使用數(shù)據(jù)庫默認(rèn)字符集latin1
mysql> show create table test1.tab4;
+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------+
| tab4 | CREATE TABLE `tab4` (
`hid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報,并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。