MariaDB/Mysql查看更改数据库和表字符编码

1. 查看数据库默认编码

MariaDB [(none)]> show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

2. 数据库单库字符编码

2.1 查看指定数据库字符编码

MariaDB [(none)]> show create database demo;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| demo     | CREATE DATABASE `demo` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

2.2 更改指定数据库字符编码

MariaDB [(none)]> ALTER DATABASE `demo` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 
MariaDB [(none)]> show create database demo;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| demo     | CREATE DATABASE `demo` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

2.3 创建数据库指定字符集

ariaDB [(none)]> create database [数据库名称] character set utf8;

3. 数据库单表字符编码

3.1 查看指定数据库指定表字符编码

MariaDB [(none)]> show create table demo.users;
+-------+-------------------------------------------------------+
| Table | Create Table                                          |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
+-------+-------------------------------------------------------+
| users | CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  `email` varchar(120) DEFAULT NULL,
  `phone_num` varchar(13) DEFAULT NULL,
  `password` varchar(64) DEFAULT NULL,
  `valid` smallint(6) DEFAULT NULL,
  `comment` text,
  `about_me` varchar(140) DEFAULT NULL,
  `last_seen` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `password` (`password`),
  UNIQUE KEY `ix_users_phone_num` (`phone_num`),
  UNIQUE KEY `ix_users_name` (`name`),
  UNIQUE KEY `ix_users_email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------+

3.2 更改指定数据库指定表字符编码

MariaDB [(none)]> alter table [数据库名称.表名称] character set utf8;

3.3 创建数据库表指定字符编码

MariaDB [(demo)]> CREATE TABLE `users` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(64) NULL DEFAULT NULL,
    `password` VARCHAR(64) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
) default COLLATE='utf8_general_ci'

3.4 更改表字段编码

MariaDB [(demo)]> alter table [表名称] change [字段名称] [字段名称] [类型] character set utf8;
MariaDB [(demo)]> alter table users change name name varchar(20) character set utf8;