MySQL查看与修改字符集的方法实例教程

站长资源 2024/11/9 佚名

23 1538 23

相思资源网 Design By www.200059.com

一、查看字符集

1.查看MYSQL数据库服务器和数据库字符集

方法一：show variables like '%character%';

方法二：show variables like 'collation%';

mysql> show variables like '%character%';
+--------------------------+--------------------------------------+
| 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  | /usr/local/mysql5535/share/charsets/ |
+--------------------------+--------------------------------------+
8 rows in set (0.00 sec)

方法一：

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)

方法二：

2.查看MYSQL所支持的字符集

show charset;

mysql> show charset;
+----------+-----------------------------+---------------------+--------+
| 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 |
| utf8mb4 | UTF-8 Unicode    | utf8mb4_general_ci |  4 |
| cp1251 | Windows Cyrillic   | cp1251_general_ci |  1 |
| utf16 | UTF-16 Unicode    | utf16_general_ci |  4 |
| cp1256 | Windows Arabic    | cp1256_general_ci |  1 |
| cp1257 | Windows Baltic    | cp1257_general_ci |  1 |
| utf32 | UTF-32 Unicode    | utf32_general_ci |  4 |
| 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 |
+----------+-----------------------------+---------------------+--------+
39 rows in set (0.00 sec)

查看MYSQL所支持的字符集

3.查看库的字符集

语法：show database status from 库名 like 表名;

mysql> show create database shiyan\G
*************************** 1. row ***************************
  Database: shiyan
Create Database: CREATE DATABASE `shiyan` /*!40100 DEFAULT CHARACTER SET gbk */
1 row in set (0.00 sec)

4.查看表的字符集

语法：show table status from 库名 like 表名;

mysql> show table status from class_7 like 'test_info';

mysql> show table status from class_7 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)

查看表的字符集

5.查看表中所有列的字符集

语法：show full columns from 表名;

mysql> show full columns from test_info;

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)

所有列的字符集

二、设置字符集

设置字符集一般有两种方法，一种是在创建表的时候设置字符集，另一种是表建成之后修改字符集。

1.创建时指定字符集

创建库的时候指定字符集：

语法：create database 库名 default character set=字符集；

create database db2 default character set=utf8

创建表的时候指定字符集：

语法：create table 表名（属性）default character set = 字符集；

mysql> create table test1(id int(6),name char(10)) default character set = 'gbk';
Query OK, 0 rows affected (0.39 sec)

2.修改字符集

修改全局字符集

/*建立连接使用的编码*/
set character_set_connection=utf8;
/*数据库的编码*/
set character_set_database=utf8;
/*结果集的编码*/
set character_set_results=utf8;
/*数据库服务器的编码*/
set character_set_server=utf8;

set character_set_system=utf8;

set collation_connection=utf8;

set collation_database=utf8;

set collation_server=utf8;

修改全局字符集

修改库的字符集

语法：alter database 库名 default character set 字符集;

alter database shiyan default character set gbk;

mysql> show create database shiyan\G
*************************** 1. row ***************************
  Database: shiyan
Create Database: CREATE DATABASE `shiyan` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)

mysql> alter database shiyan default character set gbk;
Query OK, 1 row affected (0.00 sec)

mysql> show create database shiyan\G
*************************** 1. row ***************************
  Database: shiyan
Create Database: CREATE DATABASE `shiyan` /*!40100 DEFAULT CHARACTER SET gbk */
1 row in set (0.00 sec)

修改表的字符集

语法：alter table 表名 convert to character set 字符集;

alter table test1 convert to character set utf8;

mysql> show create table test1\G
*************************** 1. row ***************************
  Table: test1
Create Table: CREATE TABLE `test1` (
 `id` int(6) DEFAULT NULL,
 `name` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk #原字符集
1 row in set (0.00 sec)

mysql> alter table test1 convert to character set utf8;
Query OK, 0 rows affected (0.58 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table test1\G
*************************** 1. row ***************************
  Table: test1
Create Table: CREATE TABLE `test1` (
 `id` int(6) DEFAULT NULL,
 `name` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 #修改后的字符集
1 row in set (0.00 sec)

修改列表的字符集

修改字段的字符集

语法：alter table 表名 modify 字段名字段属性 character set gbk；

alter table test1 modify name char(10) character set gbk;

mysql> show full columns from test1;
+-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type  | Collation  | Null | Key | Default | Extra | Privileges      | Comment |
+-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| id | int(6) | NULL   | YES |  | NULL |  | select,insert,update,references |   |
| name | char(10) | utf8_general_ci | YES |  | NULL |  | select,insert,update,references |   |
+-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.01 sec)

mysql> alter table test1 modify name char(10) character set gbk;
Query OK, 0 rows affected (0.58 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show full columns from test1;
+-------+----------+----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type  | Collation  | Null | Key | Default | Extra | Privileges      | Comment |
+-------+----------+----------------+------+-----+---------+-------+---------------------------------+---------+
| id | int(6) | NULL   | YES |  | NULL |  | select,insert,update,references |   |
| name | char(10) | gbk_chinese_ci | YES |  | NULL |  | select,insert,update,references |   |
+-------+----------+----------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.01 sec)

修改字段的字符集

总结

mysql查询字符集命令,mysql命令修改字符集,mysql查看字符集语句

标签：

mysql查询字符集命令,mysql命令修改字符集,mysql查看字符集语句

相思资源网 Design By www.200059.com

广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com

相思资源网 Design By www.200059.com

评论“MySQL查看与修改字符集的方法实例教程”

暂无MySQL查看与修改字符集的方法实例教程的评论...

www.200059.com 相思资源网

139,976影音资源

144,792福利资源

21,817软件资源

631,128技术资源

MySQL查看与修改字符集的方法实例教程

mysql查询字符集命令,mysql命令修改字符集,mysql查看字符集语句

mysql对于模糊查询like的一些汇总

MySQL 数据库 like 语句通配符模糊查询小结

评论“MySQL查看与修改字符集的方法实例教程”

RTX 5090要首发性能要翻倍！三星展示GDDR7显存

友情链接

MySQL查看与修改字符集的方法实例教程

mysql查询字符集命令,mysql命令修改字符集,mysql查看字符集语句

mysql对于模糊查询like的一些汇总

MySQL 数据库 like 语句通配符模糊查询小结

评论“MySQL查看与修改字符集的方法实例教程”

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

友情链接

RTX 5090要首发性能要翻倍！三星展示GDDR7显存