MySQL数据库/数据表/字段--字符集查看/修改

查看数据库编码：
root@localhost Wed Dec 13 11:21:00 2017 11:21:00 [liufofu]> show create database liufofu;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| liufofu | CREATE DATABASE `liufofu` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
修改数据库字符集：
root@localhost Wed Dec 13 11:21:07 2017 11:21:07 [liufofu]> alter database liufofu default character set utf8;
Query OK, 1 row affected (0.02 sec)
查看数据库编码：
root@localhost Wed Dec 13 11:21:17 2017 11:21:17 [liufofu]> show create database liufofu;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| liufofu | CREATE DATABASE `liufofu` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost Wed Dec 13 11:21:20 2017 11:21:20 [liufofu]> create table fofu(id int ,username varchar(30));
Query OK, 0 rows affected (0.14 sec)
查看表编码：
root@localhost Wed Dec 13 11:21:48 2017 11:21:48 [liufofu]> show create table fofu;
+-------+----------------------------------------------------------------------------------------------------------------
| Table | Create Table
+-------+----------------------------------------------------------------------------------------------------------------
| fofu | CREATE TABLE `fofu` (
`id` int(11) DEFAULT NULL,
`username` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
只是修改表的默认字符集：
root@localhost Wed Dec 13 11:21:54 2017 11:21:54 [liufofu]> alter table fofu default character set gbk;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
查看表编码：
root@localhost Wed Dec 13 11:22:15 2017 11:22:15 [liufofu]> show create table fofu;
+-------+----------------------------------------------------------------------------------------------------------------
| Table | Create Table
+-------+----------------------------------------------------------------------------------------------------------------
| fofu | CREATE TABLE `fofu` (
`id` int(11) DEFAULT NULL,
`username` varchar(30) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+----------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
把表默认的字符集和所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集：
root@localhost Wed Dec 13 11:22:18 2017 11:22:18 [liufofu]> alter table fofu convert to character set gbk;
Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0
查看表编码：
root@localhost Wed Dec 13 11:22:46 2017 11:22:46 [liufofu]> show create table fofu;
+-------+----------------------------------------------------------------------------------------------------------------
| Table | Create Table
+-------+----------------------------------------------------------------------------------------------------------------
| fofu | CREATE TABLE `fofu` (
`id` int(11) DEFAULT NULL,
`username` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+----------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
查看字段编码：
root@localhost Wed Dec 13 11:22:49 2017 11:22:49 [liufofu]> show full columns from fofu;
+----------+-------------+----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+----------+-------------+----------------+------+-----+---------+-------+---------------------------------+---------+
| id | int(11) | NULL | YES | | NULL | | select,insert,update,references | |
| username | varchar(30) | gbk_chinese_ci | YES | | NULL | | select,insert,update,references | |
+----------+-------------+----------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.00 sec)
修改字段的字符集：
root@localhost Wed Dec 13 11:23:10 2017 11:23:10 [liufofu]> alter table fofu change username username varchar(30) character set utf8;
Query OK, 0 rows affected (0.35 sec)
Records: 0 Duplicates: 0 Warnings: 0
查看字段编码：
root@localhost Wed Dec 13 11:24:01 2017 11:24:01 [liufofu]> show full columns from fofu;
+----------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+----------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| id | int(11) | NULL | YES | | NULL | | select,insert,update,references | |
| username | varchar(30) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
+----------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.00 sec)

转载于:https://www.cnblogs.com/liufofu/p/8031991.html