300字范文 > mysql字符集查看与设置

mysql字符集查看与设置

时间：2021-11-24 20:43:24

相关推荐

mysql字符集查看与设置

一、查看 MySQL 字符集

以下命令Windows & Linux通用。

1. 服务器和数据库

mysql> show variables like '%char%';+--------------------------+-------------------------------------+------| Variable_name| Value|......+--------------------------+-------------------------------------+------| character_set_client | utf8|...... -- 客户端字符集| character_set_connection | utf8|......| character_set_database | utf8|...... -- 数据库字符集| character_set_filesystem | binary|......| character_set_results | utf8|......| character_set_server | utf8|...... -- 服务器字符集| character_set_system | utf8|...... -- 系统元数据字符集，总是固定不变的，可以不管。| character_sets_dir | D:\MySQL Server 5.0\share\charsets\ |......+--------------------------+-------------------------------------+------

或者

mysql> status--------------mysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapperConnection id:222812Current database:Current user:root@localhostSSL:Not in useCurrent pager:stdoutUsing outfile:''Using delimiter: ;Server version:5.7.16-log MySQL Community Server (GPL)Protocol version: 10Connection: Localhost via UNIX socketServer characterset: utf8mb4Db characterset: utf8mb4Client characterset: utf8mb4Conn. characterset: utf8mb4

2. 表级

mysql> show table status from sqlstudy_db like '%countries%';+-----------+--------+---------+------------+------+-----------------+------| Name | Engine | Version | Row_format | Rows | Collation |......+-----------+--------+---------+------------+------+-----------------+------| countries | InnoDB | 10 | Compact | 11 | utf8_general_ci |......+-----------+--------+---------+------------+------+-----------------+------

3. 列级

mysql> show full columns from countries;+----------------------+-------------+-----------------+--------| Field | Type | Collation | .......+----------------------+-------------+-----------------+--------| countries_id| int(11) | NULL| .......| countries_name | varchar(64) | utf8_general_ci | .......| countries_iso_code_2 | char(2) | utf8_general_ci | .......| countries_iso_code_3 | char(3) | utf8_general_ci | .......| address_format_id | int(11) | NULL| .......+----------------------+-------------+-----------------+--------

4. 当前MySQL 支持的字符集

mysql> show charset;mysql> show char set;+----------+-----------------------------+---------------------+--------+| Charset | Description | Default collation | Maxlen |+----------+-----------------------------+---------------------+--------+| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 || dec8 | DEC West European| dec8_swedish_ci | 1 || cp850 | DOS West European| cp850_general_ci | 1 || hp8 | HP West European| hp8_english_ci | 1 || koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 || latin1 | cp1252 West European | latin1_swedish_ci | 1 || latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 || swe7 | 7bit Swedish | swe7_swedish_ci | 1 || ascii | US ASCII| ascii_general_ci | 1 || ujis | EUC-JP Japanese | ujis_japanese_ci | 3 || sjis | Shift-JIS Japanese| sjis_japanese_ci | 2 || hebrew | ISO 8859-8 Hebrew| hebrew_general_ci | 1 || tis620 | TIS620 Thai | tis620_thai_ci | 1 || euckr | EUC-KR Korean | euckr_korean_ci | 2 || koi8u | KOI8-U Ukrainian| koi8u_general_ci | 1 || gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 || greek | ISO 8859-7 Greek| greek_general_ci | 1 || cp1250 | Windows Central European | cp1250_general_ci | 1 || gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 || latin5 | ISO 8859-9 Turkish| latin5_turkish_ci | 1 || armscii8 | ARMSCII-8 Armenian| armscii8_general_ci | 1 || utf8 | UTF-8 Unicode | utf8_general_ci | 3 || ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 || cp866 | DOS Russian | cp866_general_ci | 1 || keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 || macce | Mac Central European | macce_general_ci | 1 || macroman | Mac West European| macroman_general_ci | 1 || cp852 | DOS Central European | cp852_general_ci | 1 || latin7 | ISO 8859-13 Baltic| latin7_general_ci | 1 || cp1251 | Windows Cyrillic| cp1251_general_ci | 1 || cp1256 | Windows Arabic | cp1256_general_ci | 1 || cp1257 | Windows Baltic | cp1257_general_ci | 1 || binary | Binary pseudo charset | binary | 1 || geostd8 | GEOSTD8 Georgian| geostd8_general_ci | 1 || cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 || eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |+----------+-----------------------------+---------------------+--------+

二、 Liunx下MySQL字符集修改【谨慎操作】

1. 服务器级

修改f（先备份）的character_set_server参数，需要重启mysql服务。对新建数据库、表、列，新插入数据有效。

character_set_server = utf8 #设置字符集

2. 数据库级

对新建表、列，新插入数据有效。

alter database 数据库名 character set utf8 collate utf8_bin;

也可以创建数据库时指定数据库字符集

create database mydb character set utf-8;

3. 表级

对新建列，新插入数据有效。

ALTER TABLE 表名 DEFAULT CHARACTER SET utf8 COLLATE collate utf8_bin;

生成所有表修改字符集语句：

SELECT TABLE_NAME,CONCAT('ALTER TABLE ',TABLE_NAME,' DEFAULT CHARACTER SET ',a.DEFAULT_CHARACTER_SET_NAME,' COLLATE ',a.DEFAULT_COLLATION_NAME,';') executeSQL FROM information_schema.SCHEMATA a,information_schema.TABLES bWHERE a.SCHEMA_NAME=b.TABLE_SCHEMAAND a.DEFAULT_COLLATION_NAME!=b.TABLE_COLLATIONAND b.TABLE_SCHEMA='数据库名';

4. 修改列字符集

很少用，需要修改每个列，不方便，并且执行过程中会锁表

ALTER TABLE 表名 CHANGE 列名列名 VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

生成所有列修改字符集语句：

select b.table_name,b.column_name,b.character_set_name,b.collation_name,CONCAT('ALTER TABLE ',b.table_name,' MODIFY ',b.column_name,' ',b.DATA_TYPE,'(',b.CHARACTER_MAXIMUM_LENGTH,') ',CASE WHEN b.COLUMN_DEFAULT IS NULL THEN '' ELSE CONCAT('DEFAULT \'',b.COLUMN_DEFAULT,'\'') END,' COMMENT \'',b.COLUMN_COMMENT,'\';') executeSQLfrom information_schema.TABLES a,information_schema.COLUMNS b where b.character_set_name IS NOT NULL and a.TABLE_SCHEMA=b.TABLE_SCHEMA AND a.TABLE_NAME=b.TABLE_NAMEAND a.TABLE_COLLATION!=b.COLLATION_NAMEand a.TABLE_SCHEMA='数据库名';

该SQL只是找到需要修改的列，并不是最终的语句，具体的语句还需要show create table TABLE_NAME查看后，替换字符集部分。

如果表的数据量比较大的话，时间比较长，常规的修改方式会锁表，需要使用PT工具。

pt-online-schema-change --alter ' CHANGE name name VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL' h=IP,u=root,P=3306,D=test,t=t1 --ask-pass --executept-online-schema-change --alter ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin' h=IP,u=root,P=3306,D=test,t=t1 --ask-pass --execute