常用命令 获取MySQL版本信息 1 2 $ mysql -V mysql Ver 8.0.30 for Linux on x86_64 (MySQL Community Server - GPL)
创建用户 1 mysql> create user 'username' @'%' identified by 'password' ;
其中%为允许访问的远程地址，如果是localhost，则只能本地访问；如果是%，则访问请求可以来自任何位置。
用户授权 1 mysql> grant all privileges on databaseName.* to 'username' @'%' ;
查看当前链接状态 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 mysql> \s -------------- mysql Ver 8.0.30 for Linux on x86_64 (MySQL Community Server - GPL) Connection id: 70 Current database: testdb Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 8.0.30 MySQL Community Server - GPL Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /var/lib/mysql/mysql.sock Binary data as: Hexadecimal Uptime: 17 hours 21 min 21 sec Threads: 2 Questions: 38 Slow queries: 0 Opens: 177 Flush tables: 3 Open tables: 94 Queries per second avg: 0.000 --------------
导入sql文件 1 $ mysql -uusername -p databaseName < filename.sql
节点大小 MySQL文件页大小16KB。
1 2 3 4 5 6 7 mysql> show global status like 'Innodb_page_size' ; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | Innodb_page_size | 16384 | +------------------+-------+ 1 row in set (0.24 sec)
字符集 数据库字符集是对Unicode的一套编码。
比如常用的UTF-8、GB2312、GB18030等都是相互独立的字符集，也就是相互独立的对unicode的编码。
collation用于指定数据集的排序规则，或者说是字符串的比对规则。
举个例子，我们四个字符a、b、A和B需要存储在数据库中，字符集的作用就是把这四个字符分别对应成特定的编码1、2、3和4。
1就是对a的编码，2是对b的编码，3是对A的编码，4是对B的编码，这个对应的映射关系就是字符集 （Character Set）。
如果需要比较a和b呢，就先把a和b先转化成对应的编码1和2，然后再进行比较得到a<b的结果；如果需要对大小写不敏感，则在比对A和B的时候，需要先转化成a和b再进行比较。
collation后缀规则。
后缀 全称 含义 _ai accent-insensitive 小语种里面的声调不敏感，比如e é ê è这种字符都是相等的 _as accent-sensitive 声调敏感 _ci case-insensitive 大小写不敏感 _cs case-sensitive 大小写敏感 _ks kana-sensitive 日语里面的平假名和片假名敏感，默认是不敏感的 _bin binary 根据二进制值进行比较，比较速度非常快，非常严格需要完全一致才是相等
比如utf8mb4_0900_ai_ci这个collation，utf8mb4表示字符集，0900表示Unicode 9.0规范，ai和ci的含义如上。
这篇文章有着更加深入的解释：MySQL升级8.0的新故障，utf8mb4_0900_ai_ci是啥？
获取数据库字符集 1 2 3 4 5 6 7 8 9 10 11 12 13 14 mysql> show variables like '%character%' ; +--------------------------+--------------------------------+ | 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)
获取数据库collation 1 2 3 4 5 6 7 8 9 mysql> show variables like 'collation%' ; +----------------------+--------------------+ | Variable_name | Value | +----------------------+--------------------+ | collation_connection | utf8mb4_0900_ai_ci | | collation_database | utf8mb4_0900_ai_ci | | collation_server | utf8mb4_0900_ai_ci | +----------------------+--------------------+ 3 rows in set (0.00 sec)
查看MySQL支持的所有字符集 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 mysql> show charset; +----------+---------------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+---------------------------------+---------------------+--------+ | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 | | ascii | US ASCII | ascii_general_ci | 1 | | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | binary | Binary pseudo charset | binary | 1 | | cp1250 | Windows Central European | cp1250_general_ci | 1 | | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 | | cp1256 | Windows Arabic | cp1256_general_ci | 1 | | cp1257 | Windows Baltic | cp1257_general_ci | 1 | | cp850 | DOS West European | cp850_general_ci | 1 | | cp852 | DOS Central European | cp852_general_ci | 1 | | cp866 | DOS Russian | cp866_general_ci | 1 | | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 | | euckr | EUC-KR Korean | euckr_korean_ci | 2 | | gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 | | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 | | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 | | greek | ISO 8859-7 Greek | greek_general_ci | 1 | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | | hp8 | HP West European | hp8_english_ci | 1 | | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 | | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 | | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 | | latin1 | cp1252 West European | latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 | | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | | macce | Mac Central European | macce_general_ci | 1 | | macroman | Mac West European | macroman_general_ci | 1 | | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 | | swe7 | 7bit Swedish | swe7_swedish_ci | 1 | | tis620 | TIS620 Thai | tis620_thai_ci | 1 | | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 | | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 | | utf32 | UTF-32 Unicode | utf32_general_ci | 4 | | utf8mb3 | UTF-8 Unicode | utf8mb3_general_ci | 3 | | utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 | +----------+---------------------------------+---------------------+--------+ 41 rows in set (0.00 sec)
查看某个数据库的字符集 1 2 3 4 5 6 7 mysql> show create database testdb; +----------+----------------------------------------------------------------------------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------------------------------------------------------------------------+ | testdb | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ | +----------+----------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
查看某个表的字符集 指令：show table status from [dbName] like [tableName]\G
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 mysql> show table status from testdb like 'user' \G *************************** 1. row *************************** Name: user Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 996282 Avg_row_length: 66 Data_length: 66682880 Max_data_length: 0 Index_length: 0 Data_free: 4194304 Auto_increment: 1000001 Create_time: 2023-06-23 15:28:03 Update_time: 2023-06-23 17:37:25 Check_time: NULL Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)
查看表中所有列的字符集 指令：show full columns from [tableName]
1 2 3 4 5 6 7 8 9 10 11 12 13 mysql> show full columns from user; +-------------+-----------------+--------------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-------------+-----------------+--------------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+---------+ | id | bigint unsigned | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | | name | varchar(100) | utf8mb4_0900_ai_ci | NO | | NULL | | select,insert,update,references | | | phone | varchar(20) | utf8mb4_0900_ai_ci | NO | | NULL | | select,insert,update,references | | | gender | int | NULL | NO | | NULL | | select,insert,update,references | | | age | int | NULL | NO | | NULL | | select,insert,update,references | | | create_time | timestamp | NULL | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED | select,insert,update,references | | | update_time | timestamp | NULL | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | select,insert,update,references | | +-------------+-----------------+--------------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+---------+ 7 rows in set (0.06 sec)
修改库的字符集 指令：alter database [databaseName] default character set [charset];
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 mysql> show create database testdb; +----------+----------------------------------------------------------------------------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------------------------------------------------------------------------+ | testdb | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ | +----------+----------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter database testdb default character set utf8; Query OK, 1 row affected, 1 warning (0.02 sec) mysql> show create database testdb; +----------+-------------------------------------------------------------------------------------------------------+ | Database | Create Database | +----------+-------------------------------------------------------------------------------------------------------+ | testdb | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8mb3 */ /*!80016 DEFAULT ENCRYPTION='N' */ | +----------+-------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
修改表的字符集 指令：alter table [tableName] convert to character set [charset];
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 mysql> alter table user convert to character set utf8; Query OK, 1000000 rows affected, 1 warning (6.92 sec) Records: 1000000 Duplicates: 0 Warnings: 1 mysql> show table status from testdb like 'user' \G *************************** 1. row *************************** Name: user Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 996282 Avg_row_length: 66 Data_length: 66682880 Max_data_length: 0 Index_length: 0 Data_free: 4194304 Auto_increment: 1000001 Create_time: 2023-06-27 22:12:33 Update_time: 2023-06-23 17:37:25 Check_time: NULL Collation: utf8mb3_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)
修改字段的字符集 指令：alter table user modify [columnName] [columnType] character set [charset]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 mysql> alter table user modify column name varchar(100) character set utf8; Query OK, 1000000 rows affected, 1 warning (6.75 sec) Records: 1000000 Duplicates: 0 Warnings: 1 mysql> show full columns from user; +-------------+-----------------+--------------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-------------+-----------------+--------------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+---------+ | id | bigint unsigned | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | | name | varchar(100) | utf8mb3_general_ci | YES | | NULL | | select,insert,update,references | | | phone | varchar(20) | utf8mb4_0900_ai_ci | NO | | NULL | | select,insert,update,references | | | gender | int | NULL | NO | | NULL | | select,insert,update,references | | | age | int | NULL | NO | | NULL | | select,insert,update,references | | | create_time | timestamp | NULL | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED | select,insert,update,references | | | update_time | timestamp | NULL | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | select,insert,update,references | | +-------------+-----------------+--------------------+------+-----+-------------------+-----------------------------------------------+---------------------------------+---------+ 7 rows in set (0.00 sec)