一、关于数据库引擎及配置问题
mysql 支持多种数据引擎,针对不同的数据库引擎引擎应用,应当进行相应的配置,对于初学者,最简单的方法莫过于,从/usr/share/mysql下,拷贝相应的配置到/etc/my.cnf。/usr/share/mysql下有以下配置文件:my-huge.cnf,my-innodb-heavy-4g.cnf,my-large.cnf,my-medium.cnf,my-small.cnf,直接将对应的配置文件复制到/etc/下,改名为my.cnf即可。
二、Mysql无法建InnoDB表的问题
笔者在按照以上方法改变了MYSQL配置后,发现无法创建INNODB数据引擎的数据表,原因在:http://blog.csdn.net/yufangbo/archive/2009/05/14/4182007.aspx 中有叙述,主要是,关闭MYSQL,然后找到/var/lib/mysql目录下ibdataN和ib_logfileN文件,不管多少个,都删掉,然后,重启mysql即可。
三、charset问题
由于涉及到中文,数据存储存在显示乱码,存储超线问题,比如,varchar(20)字段,到底是存储20个中文还是6个中文或者10中文?在mssql中,中文已经和英文一样对待了,所以,我们的意图应该是存储20个中文。
同样,我们在对字符运算过程中也存在问题,比如length,substring等函数,如果中文是分几个字节存储,如果我们substring后,发现是乱码怎么办?这完全有可能。
笔者尝试了多种方法,最后发现全部采用utf8是最佳方案,具体做法如下:
1、配置mysql
打开my.cnf以后,在[client]和[mysqld]下面均加上default-character-set=utf8,保存并关闭,重启mysql。
2、查看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 数据表(table) 的字符集。
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> 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> 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 |
| 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 |
+----------+-----------------------------+---------------------+--------+
查看mysql字符集是为了我们确认当前使用了什么字符集。
3、创建数据库和数据表,习惯性加上charset选项
create database sample default charset=utf8;
对已有的数据库进行修改:
ALTER DATABASE sample
CHARACTER SET utf8
DEFAULT CHARACTER SET utf8
COLLATE utf8_general_ci
DEFAULT COLLATE utf8_general_ci;
create table tbluser (
user_id varchar(20) primary key,
user_name varchar(20) not null,
user_pwd varchar(20) not null,
user_birthday date not null,
user_sex char(1) not null,
user_email varchar(50) not null,
user_role char(1) not null,
user_status char(1) not null
)default charset=utf8;
4、统计字符串长度,使用char_length
length: 是计算字段的长度一个汉字是算三个字符,一个数字或字母算一个字符
char_length:不管汉字还是数字或者是字母都算是一个字符
采用以上办法,彻底解决了中文字符乱码和中文字符运算问题。