Shulou(Shulou.com)06/01 Report--

Summary

1. Create database will automatically generate a file db.opt, which stores the default character set of the database. When show create database displays the default character set of the database, that is, the character set of db.opt.

2. The loss of this file does not affect the operation of the database. When you create a new table after the file is lost, if you cannot find the default character set of the database, character_set_server is regarded as the default character set of the database, and the character_set_server character set is displayed when show create database.

Mysql > show variables like 'character_set_server'

+-+ +

| | Variable_name | Value |

+-+ +

| | character_set_server | latin1 |

+-+ +

The test1 library does not specify a character set, using the character_set_ server value

Mysql > create database test1

[root@mydb test1] # cat / var/lib/mysql/test1/db.opt

Default-character-set=latin1

Default-collation=latin1_swedish_ci

The test2 library specifies the character set 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

The default character set of the test1 library latin1,show create database displays the default character set latin1

The tab1 table uses the database default character set 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)

The default character set of the test1 library is missing because the db.opt file no longer exists, and show create database displays the character_set_server character set latin1

The tab2 table has no way to use the database default character set, but uses the character_set_server character set 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)

The default character set of the test1 library is missing because the db.opt file no longer exists, and show create database displays the character_set_server character set latin7

The tab2 table has no way to use the database default character set, but uses the character_set_server character set 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)

The default character set of the test1 library latin1,show create database displays the default character set latin1

The tab4 table uses the database default character set 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)

The tab1 table uses the database default character set 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 table uses character_set_server character set 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 table uses character_set_server character set latin7

Mysql > show create table test1.tab3

+-+ +

| | Table | Create Table |

+-+ +

| | tab3 | CREATE TABLE `tab3` (

`hid` int (11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin7 |

+-+ +

The tab4 table uses the database default character set latin1

Mysql > show create table test1.tab4

+-+ +

| | Table | Create Table |

+-+ +

| | tab4 | CREATE TABLE `tab4` (

`hid` int (11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.