查看 MYSQL 所支持的检验规则
- 使用 SHOW CHARSET 语句可以查看所有支持的字符集
语法
SHOW COLLATION;
示例
mysql> SHOW COLLATION;
+----------------------+-----------+-----+----------+-----------+----------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+----------------------+-----------+-----+----------+-----------+----------+
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
| big5_bin | big5 | 84 | | Yes | 1 |
| dec8_swedish_ci | dec8 | 3 | Yes | | 0 |
| ........ | .... | .. | ... | ... | . |
| eucjpms_bin | eucjpms | 98 | | Yes | 1 |
+----------------------+-----------+-----+----------+-----------+----------+
127 rows in set (0.00 sec)
查看数据库服务器和数据库字符集
- 使用 SHOW VARIABLES 语句可以查看数据库服务器和数据库的校验规则
语法
SHOW VARIABLES [LIKE ''];
示例
mysql> SHOW VARIABLES LIKE 'collation%';
+------------------------+--------------------+
| Variable_name | Value
|
+------------------------+--------------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+------------------------+--------------------+
3 rows in set (0.00 sec)
示例
mysql> SHOW VARIABLES LIKE 'character_set_server'
+--------------------------+--------------------+
| Variable_name |
Value |
+--------------------------+--------------------+
| character_set_server |
utf8_general_ci |
+--------------------------+--------------------+
1 rows in set (0.00
sec)
查看创建数据库的语句，来查看检验规则
- mysql中可以通过 SHOW CREATE DATABASE 语句查看创建的数据库的语句，从生成的语句中也可以得到检验规则
示例
mysql> SHOW CREATE DATABASE test\G
*************************** 1. row ***************************
Database: test
Create Database: CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci */ /*!80014 DEFAULT ENCRYPTION='N' */
使用 SELECT 语句查看数据库的检验规则
- mysql 中数据库的信息储存在 information_schema 表中，可以通过此表进行查询
语法
SELECT schema_name,default_collation_name
FEOM information_schema.schemata
[ WHERE schema_name = <数据表名> ];
示例
mysql> SELECT schema_name,default_character_set_name
> FROM information_schema.schemata
> WHERE schema_name = 'test_db';
+-----------------+-----------------------------------+
| schema_name |
default_collation_name |
+-----------------+-----------------------------------+
| test_db | utf8_general_ci |
+-----------------+-----------------------------------+
1 row in set (0.00 sec)
- 使用地址@查找字段，需要使用数据库
语法
USE test_db;
SELECT @@collations;
示例
mysql> USE test_db;
mysql> SELECT @@collations_database;
+-----------------------------------+
| @@collation_database |
+-----------------------------------+
| utf8_general_ci
|
+-----------------------------------+
1 row in set (0.00 sec)
查看数据表的校验规则
- mysql中使用SHOW TABLE STATUS FROM 语句获取数据表的信息，其中有检验规则的信息
语法
SHOW TABLE STATUS FROM <数据库名> [LIKE <数据库表名>]
示例
mysql> SHOW TABLE STATUS
FROM test_db LIKE 'test_info';
+------------+----------+----------+----------------+-------+--------------------+------------------------+----------------+---------------+-------------------+------------+
| Name |
Engine |
Version | Row_format |
Rows | Avg_row_length | Data_leate_time |
Update_time | Check_time |
Collation |
Checksum |
+------------+----------+----------+----------------+-------+--------------------+------------------------+----------------+---------------+-------------------+------------+
| test_info | InnoDB | 10 |
Compact | 10 | 1638 | 17-12-05 19:01:55 | NULL | NULL |
utf8_general_ci | NULL |
+------------+----------+----------+----------------+-------+--------------------+------------------------+----------------+---------------+-------------------+------------+
1 row in set (0.00
sec)
查看表中所有列的校验规则
- 在mysql中可以使用SHOW FULL COLUMNS 查看所有字段的信息
语法
SHOW FULL COLUMNS FROM <数据库名>
示例
mysql> show full columns from test_info;
+-------+-----------+-------------------+-------+-----+----------+--------+------------------------------------+-----------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges
| Comment |
+-------+-----------+-------------------+-------+-----+----------+--------+------------------------------------+-----------+
| id | int(3) | NULL | NO | PRI | NULL | | select,insert,update,references | |
| name | char(12) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| dorm | char(10) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| addr | char(12) | utf8_general_ci | YES | | 未知 | | select,insert,update,references | |
| score | int(3) | NULL | YES | | NULL | | select,insert,update,references | |
+-------+-----------+--------------------+------+-----+----------+--------+-------------------------------------+----------+
5 rows in set (0.00 sec)
来自 <https://www.cnblogs.com/yangmingxianshen/p/7999428.html>