查看修改MySQL字符集
查看修改MySQL字符集
http://blog.sina.com.cn/s/blog_70ac6bec01016fts.html
?
查看修改MySQL字符集
?(2012-08-22 09:53:21) 轉(zhuǎn)載▼標(biāo)簽:? 字符集?mysql數(shù)據(jù)庫?mysql字符集查看?mysql啟動(dòng)命令?修改mysql字符集 | 分類:?網(wǎng)站開發(fā) |
一、查看MySQL數(shù)據(jù)庫服務(wù)器和數(shù)據(jù)庫MySQL字符集。
為了不出現(xiàn)亂碼,我們需要統(tǒng)一修改這些參數(shù),比如我要把數(shù)據(jù)庫的默認(rèn)字符集改為utf8,如下所示
在[client]下添加如下參數(shù) default-character-set=utf8
在[mysqld]下添加如下參數(shù) default-character-set=utf8
然后重啟mysql服務(wù),再查看字符集(要重新登錄客戶端)
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的字符集分的比較細(xì),可以采用默認(rèn)值,也可以指定值
A.數(shù)據(jù)庫字符集
使用默認(rèn)字符集的庫 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
?
使用默認(rèn)庫字符集的表 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)
創(chuàng)建指定字符集的表 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: 0??Duplicates: 0??Warnings: 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>
從上面可以看到,修改表的字符集時(shí),不會(huì)影響已經(jīng)存在列的字符集,但新增列將繼承表的字符集,如下
mysql> alter table t2 add a2 varchar(10); Query OK, 0 rows affected (0.01 sec) Records: 0??Duplicates: 0??Warnings: 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>
?
如果想更改已經(jīng)存在表和字符列的字符集,要用如下
mysql> alter table t2 convert to character set latin1; Query OK, 0 rows affected (0.01 sec) Records: 0??Duplicates: 0??Warnings: 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.字段字符集
?
使用默認(rèn)和指定字符集創(chuàng)建的列 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: 0??Duplicates: 0??Warnings: 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>
?
結(jié)論:字符集從數(shù)據(jù)庫、表、列的字符集是逐級繼承的關(guān)系
?
查看字符集命令 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;//更改表的現(xiàn)存列字符集 alter table t2 default character set utf8;??//更能改表的默認(rèn)字符集 alter table t3 change a2 a2 varchar(10) character set utf8; //更能改字段的字符集
?
2.修改mysql的默認(rèn)存儲(chǔ)引擎
mysql> show variables like 'storage%'; +----------------+--------+ | Variable_name??| Value??| +----------------+--------+ | storage_engine | MyISAM | +----------------+--------+ 1 row in set (0.01 sec)
mysql>
?
在[mysqld]下添加如下參數(shù) default-storage-engine=innodb
?
重啟mysql服務(wù),查看
mysql> show variables like 'storage%'; +----------------+--------+ | Variable_name??| Value??| +----------------+--------+ | storage_engine | InnoDB | +----------------+--------+ 1 row in set (0.01 sec)
mysql>
?
創(chuàng)建默認(rèn)字符集的表 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)
?
更改表的默認(rèn)字符集 mysql> alter table t4 engine myisam; Query OK, 0 rows affected (0.06 sec) Records: 0??Duplicates: 0??Warnings: 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數(shù)據(jù)表(table)的MySQL字符集。三、查看MySQL數(shù)據(jù)列(column)的MySQL字符集。
MySQL修改默認(rèn)字符集
修改mysql的默認(rèn)字符集是通過修改它的配置文件來實(shí)現(xiàn)的。一般分兩種情況:
方法一:
Windows平臺(tái)
?????windows下的mysql配置文件是my.ini
????一般在C:\Program Files\MySQL\MySQL Server 5.0
????default-character-set=gbk #或gb2312,big5,utf8 ????然后重新啟動(dòng)mysql:運(yùn)行->services.msc->停止MySql->啟動(dòng)???OK
????或 ?????/etc/init.d/mysql restart ?????或用其他方法重新啟動(dòng),就生效了。
Unix平臺(tái) ????linux下的mysql配置文件是my.cnf
????一般是/etc/my.cnf,如果找不到可以用find命令找一下: ?????find / -iname my.cnf ????在這個(gè)文件里面加上 ????default-character-set=gbk #或gb2312,big5,utf8 ????然后重新啟動(dòng)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 :
如何啟動(dòng)/停止/重啟MySQL
啟動(dòng)、停止、重啟 MySQL 是每個(gè)擁有獨(dú)立主機(jī)的站長必須要撐握的操作,下面為大家簡單介紹一下操作方法:
一、啟動(dòng)方式?
1、使用 service 啟動(dòng):service mysqld start
2、使用 mysqld 腳本啟動(dòng):/etc/inint.d/mysqld start
3、使用 safe_mysqld 啟動(dòng):safe_mysqld&
二、停止
1、使用 service 啟動(dòng):service mysqld stop
2、使用 mysqld 腳本啟動(dòng):/etc/inint.d/mysqld stop
3、mysqladmin shutdown
三、重啟
1、使用 service 啟動(dòng):service mysqld restart
2、使用 mysqld 腳本啟動(dòng):/etc/inint.d/mysqld restart
終極方法
mysql> set names gbk;?
?
部分原文:http://blog.csdn.net/wyzxg/article/details/7581415
轉(zhuǎn)載于:https://www.cnblogs.com/MYSQLZOUQI/p/3780967.html
總結(jié)
以上是生活随笔為你收集整理的查看修改MySQL字符集的全部內(nèi)容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: SQL Server 2008 R2如何
- 下一篇: ArcGIS for Android示例