查看修改MySQL字符集
/s/blog_70ac6bec01016fts.html
查看修改MySQL字符集
(-08-22 09:53:21)转载▼MySQL字符集多种多样，下面为列举了其中三种最常见的字符集查看方法，该方法供您参考，希望对学习MySQL数据库能有所启迪。一、查看MySQL数据库服务器和数据库MySQL字符集。
mysql>showvariableslike'%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:\MySQLServer5.0\share\charsets\|...... +--------------------------+-------------------------------------+------mysql的字符集分为几类 A.客户端字符集：通过系统变量”character_set_client“表示，通知server端，客户端提交的sql语句编码格式 B.连接字符集：通过系统变量”character_set_connectiont“表示，server端翻译sql语句时，使用的编码格式 C.结果集字符集：通过系统变量”character_set_results“表示，server端返回结果集之前把结果集转换成的编码格式 D.存储字符集：通过系统变量”character_set_results“和“character_set_server”表示，是数据在存储引擎里编码格式
为了不出现乱码，我们需要统一修改这些参数，比如我要把数据库的默认字符集改为utf8，如下所示
在[client]下添加如下参数 default-character-set=utf8
在[mysqld]下添加如下参数 default-character-set=utf8
然后重启mysql服务，再查看字符集（要重新登录客户端）
mysql>
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>
mysql的字符集分的比较细，可以采用默认值，也可以指定值
A.数据库字符集
使用默认字符集的库 mysql> create database db1; Query OK, 1 row affected (0.01 sec)
mysql> show create database db1; +----------+--------------------------------------------------------------+ | Database | Create Database| +----------+--------------------------------------------------------------+ | db1| CREATE DATABASE `db1` | +----------+--------------------------------------------------------------+ 1 row in set (0.00 sec)
指定字符集的库 mysql> create database db2 default character set latin1; Query OK, 1 row affected (0.01 sec)
mysql> show create database db2; +----------+----------------------------------------------------------------+ | Database | Create Database| +----------+----------------------------------------------------------------+ | db2| CREATE DATABASE `db2` | +----------+----------------------------------------------------------------+ 1 row in set (0.00 sec)
修改库的字符集 mysql> alter database db2 default character set utf8; Query OK, 1 row affected (0.00 sec)
mysql> show create database db2; +----------+--------------------------------------------------------------+ | Database | Create Database| +----------+--------------------------------------------------------------+ | db2| CREATE DATABASE `db2` | +----------+--------------------------------------------------------------+ 1 row in set (0.00 sec)
B.表字符集
mysql> use db2; Database changed
使用默认库字符集的表 mysql> create table t1(a varchar(10)); Query OK, 0 rows affected (0.01 sec)
mysql> show create table t1; +-------+-----------------------------------------------------------------------------------------+ | Table | Create Table| +-------+-----------------------------------------------------------------------------------------+ | t1| CREATE TABLE `t1` ( `a` varchar(10) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+-----------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
创建指定字符集的表 mysql> create table t2(a varchar(10)) default character set latin1;; Query OK, 0 rows affected (0.01 sec)
ERROR: No query specified
mysql> show create table t2; +-------+-------------------------------------------------------------------------------------------+ | Table | Create Table| +-------+-------------------------------------------------------------------------------------------+ | t2| CREATE TABLE `t2` ( `a` varchar(10) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+-------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
更改表的字符集 mysql> alter table t2 default character set utf8; Query OK, 0 rows affected (0.01 sec) Records: 0Duplicates: 0Warnings: 0
mysql> show create table t2; +-------+--------------------------------------------------------------------------------------------------------------+ | Table | Create Table| +-------+--------------------------------------------------------------------------------------------------------------+ | t2| CREATE TABLE `t2` ( `a` varchar(10) character set latin1 default NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+--------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
mysql>
从上面可以看到，修改表的字符集时，不会影响已经存在列的字符集，但新增列将继承表的字符集，如下
mysql> alter table t2 add a2 varchar(10); Query OK, 0 rows affected (0.01 sec) Records: 0Duplicates: 0Warnings: 0
mysql> show create table t2; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table| +-------+-----------------------------------------------------------------------------------------------------------------------------------------------+ | t2| CREATE TABLE `t2` ( `a` varchar(10) character set latin1 default NULL, `a2` varchar(10) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)
mysql>
如果想更改已经存在表和字符列的字符集，要用如下
mysql> alter table t2 convert to character set latin1; Query OK, 0 rows affected (0.01 sec) Records: 0Duplicates: 0Warnings: 0
mysql> show create table t2; +-------+----------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table| +-------+----------------------------------------------------------------------------------------------------------------------------+ | t2| CREATE TABLE `t2` ( `a` varchar(10) default NULL, `a2` varchar(10) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+----------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)
C.字段字符集
使用默认和指定字符集创建的列 mysql> create table t3 (a1 varchar(10),a2 varchar(10) character set latin1); Query OK, 0 rows affected (0.04 sec)
mysql> show create table t3; +-------+------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table| +-------+------------------------------------------------------------------------------------------------------------------------------------------------+ | t3| CREATE TABLE `t3` ( `a1` varchar(10) default NULL, `a2` varchar(10) character set latin1 default NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
mysql>
查看列的字符集 mysql> show full columns from t3; +-------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type| Collation| Null | Key | Default | Extra | Privileges| Comment | +-------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+ | a1| varchar(10) | utf8_general_ci| YES|| NULL|| select,insert,update,references || | a2| varchar(10) | latin1_swedish_ci | YES|| NULL|| select,insert,update,references || +-------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+ 2 rows in set (0.01 sec)
修改列的字符集 mysql> alter table t3 change a2 a2 varchar(10) character set utf8; Query OK, 0 rows affected (0.03 sec) Records: 0Duplicates: 0Warnings: 0
mysql> show full columns from t3; +-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type| Collation| Null | Key | Default | Extra | Privileges| Comment | +-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+ | a1| varchar(10) | utf8_general_ci | YES|| NULL|| select,insert,update,references || | a2| varchar(10) | utf8_general_ci | YES|| NULL|| select,insert,update,references || +-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+ 2 rows in set (0.00 sec)
mysql>
结论：字符集从数据库、表、列的字符集是逐级继承的关系
查看字符集命令 mysql> show create table t4; //显示表字符集 mysql> show create database db2; //显示库字符集 mysql> show full columns from t4; //显示列字符集
alter database db2 default character set utf8; //更改库的字符集 alter table t2 convert to character set latin1;//更改表的现存列字符集 alter table t2 default character set utf8;//更能改表的默认字符集 alter table t3 change a2 a2 varchar(10) character set utf8; //更能改字段的字符集
2.修改mysql的默认存储引擎
mysql> show variables like 'storage%'; +----------------+--------+ | Variable_name| Value| +----------------+--------+ | storage_engine | MyISAM | +----------------+--------+ 1 row in set (0.01 sec)
mysql>
在[mysqld]下添加如下参数 default-storage-engine=innodb
重启mysql服务，查看
mysql> show variables like 'storage%'; +----------------+--------+ | Variable_name| Value| +----------------+--------+ | storage_engine | InnoDB | +----------------+--------+ 1 row in set (0.01 sec)
mysql>
创建默认字符集的表 mysql> create table t4 (a1 int); Query OK, 0 rows affected (0.01 sec)
mysql> show create table t4; +-------+--------------------------------------------------------------------------------------+ | Table | Create Table| +-------+--------------------------------------------------------------------------------------+ | t4| CREATE TABLE `t4` ( `a1` int(11) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+--------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
更改表的默认字符集 mysql> alter table t4 engine myisam; Query OK, 0 rows affected (0.06 sec) Records: 0Duplicates: 0Warnings: 0
mysql> show create table t4; +-------+--------------------------------------------------------------------------------------+ | Table | Create Table| +-------+--------------------------------------------------------------------------------------+ | t4| CREATE TABLE `t4` ( `a1` int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | +-------+--------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
mysql>
二、查看MySQL数据表（table）的MySQL字符集。mysql>showtablestatusfromsqlstudy_dblike'%countries%'; +-----------+--------+---------+------------+------+-----------------+------ |Name|Engine|Version|Row_format|Rows|Collation|...... +-----------+--------+---------+------------+------+-----------------+------ |countries|InnoDB|10|Compact|11|utf8_general_ci|...... +-----------+--------+---------+------------+------+-----------------+------
三、查看MySQL数据列（column）的MySQL字符集。
mysql>showfullcolumnsfromcountries; +----------------------+-------------+-----------------+-------- |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|....... +----------------------+-------------+-----------------+--------
MySQL修改默认字符集
修改mysql的默认字符集是通过修改它的配置文件来实现的。一般分两种情况：
方法一:
Windows平台
windows下的mysql配置文件是my.ini
一般在C:\Program Files\MySQL\MySQL Server 5.0
default-character-set=gbk #或gb2312,big5,utf8 然后重新启动mysql:运行->services.msc->停止MySql->启动OK
或 /etc/init.d/mysql restart 或用其他方法重新启动，就生效了。
Unix平台 linux下的mysql配置文件是f
一般是/etc/f，如果找不到可以用find命令找一下： find / -iname f 在这个文件里面加上 default-character-set=gbk #或gb2312,big5,utf8 然后重新启动mysql net stop mysql net start mysql
方法二:
通过MySQL命令行修改:
mysql> set character_set_client=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_connection=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_database=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_results=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_server=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_system=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> set collation_connection=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> set collation_database=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> set collation_server=utf8;
Query OK, 0 rows affected (0.01 sec)
查看:
mysql> show variables like 'character_set_%';
utf8 改为 gb2312
重启mysql :
如何启动/停止/重启MySQL
启动、停止、重启 MySQL 是每个拥有独立主机的站长必须要撑握的操作，下面为大家简单介绍一下操作方法：
一、启动方式
1、使用 service 启动：service mysqld start
2、使用 mysqld 脚本启动：/etc/inint.d/mysqld start
3、使用 safe_mysqld 启动：safe_mysqld&
二、停止
1、使用 service 启动：service mysqld stop
2、使用 mysqld 脚本启动：/etc/inint.d/mysqld stop
3、mysqladmin shutdown
三、重启
1、使用 service 启动：service mysqld restart
2、使用 mysqld 脚本启动：/etc/inint.d/mysqld restart
终极方法
mysql> set names gbk;
部分原文：/wyzxg/article/details/7581415
