MySQL查看编码及排序规则

My.Cnf文件

# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL

default-storage-engine=INNODB
character_set_server=utf8mb4
innodb_default_row_format=DYNAMIC
innodb_log_file_size=2G
wait_timeout=246400

#server-id=1
#symbolic-links=0
log-error=/var/lib/mysql/mysqld.log

# 表名不区分大小写
lower_case_table_names=1
# 解决错误：this is incompatible with sql_mode=only_full_group_by
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION



# Custom config should go here
!includedir /etc/mysql/conf.d/

DockerStart

#!/bin/bash
# author:WinJayX
# date:2020-05-07
# Maintainer WinJayX <WinJayX@Gmail.com>
# func:Docker Demo
# !!! -p 参数说明 -p ：前的为宿主机的端口，后的为容器业务所需的端口!!!
# --privileged=true 特权模式 --restart unless-stopped 重启策略，除非停止的状态
#    --security-opt="seccomp=unconfined" ：解决在查看mysql的docker日志时，出现很多`mbind: Operation not permitted`信息
#    --character-set-server=utf8mb4 \
#    --collation-server=utf8mb4_0900_ai_ci \
docker stop MySQL
docker rm -f MySQL
docker container run -d \
    --volume /etc/localtime:/etc/localtime:ro \
    --volume `pwd`/Data:/var/lib/mysql \
    --volume `pwd`/MySQL-Conf:/etc/mysql \
    --user root \
    --name MySQL \
    --restart always \
    -p 3306:3306 \
    -e MYSQL_ROOT_PASSWORD=YourPassWord \
    --security-opt="seccomp=unconfined" \
    mysql:8.0 \
    --explicit_defaults_for_timestamp=true \
    --lower_case_table_names=1 \
    --max_allowed_packet=128M \
    --default-authentication-plugin=mysql_native_password

查看数据库的排序规则

mysql> show variables like 'collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | utf8_general_ci   |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

查看数据库的编码格式

mysql>  show variables like 'char%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)

查看字段的排序规则

mysql> show create table tablename;

查看表字段信息

mysql> desc tablename;

查看当前数据库编码

mysql> create database aws;
Query OK, 1 row affected (0.08 sec)
mysql> use aws;
Database changed
mysql>  show variables like 'character_set_database';
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| character_set_database | utf8mb4 |
+------------------------+---------+
1 row in set (0.00 sec)

修改当前数据库的编码

mysql> use databasename;
Database changed

mysql> alter database databasename character set latin1;  #将之前的utf8编码修改为latin1编码
Query OK, 1 row affected (0.00 sec)
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| character_set_database | latin1 |
+------------------------+--------+
1 row in set (0.00 sec)

查看字段编码

SHOW FULL COLUMNS FROM tablename;

修改字段的字符集

ALTER TABLE tablename CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];

如：ALTER TABLE users CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;

修改各种编码

mysql> set collation_server=utf8mb4;
mysql> set collation_database=utf8mb4;
mysql> set character_set_client=utf8mb4;
mysql> set collation_connection=utf8mb4;

mysql> set character_set_server=utf8mb4;
mysql> set character_set_system=utf8mb4;
mysql> set character_set_results=utf8mb4;
mysql> set character_set_database=utf8mb4;
mysql> set character_set_connection=utf8mb4;