【运维经】第62章——mysql字符集编码的那些事儿

最新推荐文章于 2024-11-01 16:03:19 发布

夏克

最新推荐文章于 2024-11-01 16:03:19 发布

阅读量181

点赞数

分类专栏：运维经文章标签： mysql mariadb sql 数据库

本文链接：https://blog.csdn.net/xk_xx/article/details/106121640

版权

运维经专栏收录该内容

74 篇文章 4 订阅

订阅专栏

运维经–目录

mysql字符集的那些事儿

准备实验环境环境

MariaDB [(none)]> CREATE DATABASE test_db character set utf8;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test_db            |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> use test_db;
Database changed
MariaDB [test_db]> create table tb_books (
    ->     name varchar(45) not null,
    ->     price double not null,
    ->     bookCount int not null,
    ->     author varchar(45) not null ) default charset = utf8;
Query OK, 0 rows affected (0.05 sec)

MariaDB [test_db]> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_books          |
+-------------------+
1 row in set (0.00 sec)

MariaDB [test_db]>

查看数据库字符集

MariaDB [test_db]> show variables like 'character_set_database';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.00 sec)

查看数据表的字符集

MariaDB [test_db]> show create table tb_books;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                             |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_books | CREATE TABLE `tb_books` (
  `name` varchar(45) NOT NULL,
  `price` double NOT NULL,
  `bookCount` int(11) NOT NULL,
  `author` varchar(45) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test_db]>

修改数据库的编码格式

MariaDB [test_db]> alter database test_db character set gb2312;
Query OK, 1 row affected (0.01 sec)

MariaDB [test_db]> show variables like 'character_set_database';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| character_set_database | gb2312 |
+------------------------+--------+
1 row in set (0.00 sec)

MariaDB [test_db]>

修改数据表格字符集

MariaDB [test_db]> show create table tb_books;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                             |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_books | CREATE TABLE `tb_books` (
  `name` varchar(45) NOT NULL,
  `price` double NOT NULL,
  `bookCount` int(11) NOT NULL,
  `author` varchar(45) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test_db]> alter table tb_books character set gb2312;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test_db]> show create table tb_books;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                     |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_books | CREATE TABLE `tb_books` (
  `name` varchar(45) CHARACTER SET utf8 NOT NULL,
  `price` double NOT NULL,
  `bookCount` int(11) NOT NULL,
  `author` varchar(45) CHARACTER SET utf8 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test_db]>

修改字段编码格式

MariaDB [test_db]> alter table tb_books change name name varchar(45) character set utf8 not null;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test_db]> show create table tb_books;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                     |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_books | CREATE TABLE `tb_books` (
  `name` varchar(45) CHARACTER SET utf8 NOT NULL,
  `price` double NOT NULL,
  `bookCount` int(11) NOT NULL,
  `author` varchar(45) CHARACTER SET utf8 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)