电子商务网站建设实践报告摘要,禅城网站设计,做网站的网页设计用cdr吗,张家界旅游网站官网查看修改MySQL字符集 http://blog.sina.com.cn/s/blog_70ac6bec01016fts.html 查看修改MySQL字符集 (2012-08-22 09:53:21) 转载▼标签#xff1a; 字符集 mysql数据库 mysql字符集查看 mysql启动命令 修改mysql字符集 分类#xff1a; 网站开发MySQL字符集多种多样…查看修改MySQL字符集 http://blog.sina.com.cn/s/blog_70ac6bec01016fts.html 查看修改MySQL字符集 (2012-08-22 09:53:21) 转载▼ 标签 字符集 mysql数据库 mysql字符集查看 mysql启动命令 修改mysql字符集 分类 网站开发 MySQL字符集多种多样下面为列举了其中三种最常见的字符集查看方法该方法供您参考希望对学习MySQL数据库能有所启迪。 一、查看MySQL数据库服务器和数据库MySQL字符集。 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的字符集分为几类 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-setutf8 在[mysqld]下添加如下参数 default-character-setutf8 然后重启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 ) ENGINEInnoDB DEFAULT CHARSETutf8 | ------------------------------------------------------------------------------------------------ 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 ) ENGINEInnoDB DEFAULT CHARSETlatin1 | -------------------------------------------------------------------------------------------------- 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 ) ENGINEInnoDB DEFAULT CHARSETutf8 | --------------------------------------------------------------------------------------------------------------------- 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: 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 ) ENGINEInnoDB DEFAULT CHARSETutf8 | ------------------------------------------------------------------------------------------------------------------------------------------------------ 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: 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 ) ENGINEInnoDB DEFAULT CHARSETlatin1 | ----------------------------------------------------------------------------------------------------------------------------------- 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 ) ENGINEInnoDB DEFAULT CHARSETutf8 | ------------------------------------------------------------------------------------------------------------------------------------------------------- 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 结论字符集从数据库、表、列的字符集是逐级继承的关系 查看字符集命令 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-engineinnodb 重启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 ) ENGINEInnoDB DEFAULT CHARSETutf8 | --------------------------------------------------------------------------------------------- 1 row in set (0.00 sec) 更改表的默认字符集 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 ) ENGINEMyISAM DEFAULT CHARSETutf8 | --------------------------------------------------------------------------------------------- 1 row in set (0.00 sec) mysql 二、查看MySQL数据表table的MySQL字符集。mysql show table status from sqlstudy_db like %countries%; --------------------------------------------------------------------- | Name | Engine | Version | Row_format | Rows | Collation |...... --------------------------------------------------------------------- | countries | InnoDB | 10 | Compact | 11 | utf8_general_ci |...... --------------------------------------------------------------------- 三、查看MySQL数据列column的MySQL字符集。 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 | ....... ------------------------------------------------------------ MySQL修改默认字符集 修改mysql的默认字符集是通过修改它的配置文件来实现的。一般分两种情况 方法一: Windows平台 windows下的mysql配置文件是my.ini 一般在C:\Program Files\MySQL\MySQL Server 5.0 default-character-setgbk #或gb2312,big5,utf8 然后重新启动mysql:运行-services.msc-停止MySql-启动 OK 或 /etc/init.d/mysql restart 或用其他方法重新启动就生效了。 Unix平台 linux下的mysql配置文件是my.cnf 一般是/etc/my.cnf如果找不到可以用find命令找一下 find / -iname my.cnf 在这个文件里面加上 default-character-setgbk #或gb2312,big5,utf8 然后重新启动mysql net stop mysql net start mysql 方法二: 通过MySQL命令行修改:mysql set character_set_clientutf8;Query OK, 0 rows affected (0.00 sec)mysql set character_set_connectionutf8;Query OK, 0 rows affected (0.00 sec)mysql set character_set_databaseutf8;Query OK, 0 rows affected (0.00 sec)mysql set character_set_resultsutf8;Query OK, 0 rows affected (0.00 sec)mysql set character_set_serverutf8;Query OK, 0 rows affected (0.00 sec)mysql set character_set_systemutf8;Query OK, 0 rows affected (0.01 sec)mysql set collation_connectionutf8;Query OK, 0 rows affected (0.01 sec)mysql set collation_databaseutf8;Query OK, 0 rows affected (0.01 sec)mysql set collation_serverutf8;Query OK, 0 rows affected (0.01 sec)查看:mysql show variables like character_set_%;utf8 改为 gb2312重启mysql :如何启动/停止/重启MySQL启动、停止、重启 MySQL 是每个拥有独立主机的站长必须要撑握的操作下面为大家简单介绍一下操作方法一、启动方式 1、使用 service 启动service mysqld start2、使用 mysqld 脚本启动/etc/inint.d/mysqld start3、使用 safe_mysqld 启动safe_mysqld二、停止1、使用 service 启动service mysqld stop2、使用 mysqld 脚本启动/etc/inint.d/mysqld stop3、mysqladmin shutdown三、重启1、使用 service 启动service mysqld restart2、使用 mysqld 脚本启动/etc/inint.d/mysqld restart 终极方法 mysql set names gbk; 部分原文http://blog.csdn.net/wyzxg/article/details/7581415 转载于:https://www.cnblogs.com/MYSQLZOUQI/p/3780967.html
