公司开发的应用,一般要兼容大多数主流数据库,postgres,mysql,sql server,sqlserver,由于postgres的免费,用的比较多,客户现场用mysql跟sql server较多,oracle太贵了,除非很大型的项目.个人非常喜欢postgres,功能非常强大,插件繁多,系统非常稳定,并发性能强.相比mysql,没有那么多的编码设置,乱码问题.实在清爽,我一般以postgres作初步测试,然后再转向其它数据库平台.
之前开发过一个自动建表库(包括权限表/采样表/统计表),能够根据不同的数据库配置,自动生成相应的数据表.各个平台都比较正常,可是一到mysql上总是会报错:
一. Specified key was too long; max key length is 767
建表SQL:
CREATE TABLE (
TAG VARCHAR(320) NOT NULL,
DATE1 DATE,
ZIP SMALLINT,
DATA BLOB,
PRIMARY KEY(TAG,DATE1)
);
从错误描述来看,键长度过大>767,导致建表失败.查看下数据库编码都是UTF-8.难道与编码有关系吗?
谷歌发现上述的键指的是主键,也就是主键长度过长,LATIN1单个字符长度为1个字节,GBK为2个字节,UTF8为3个字节.此时长度为:3*(320+3)>767
方法一:减小TAG长度,设置为:246 (765/3-9)
方法二:强制指定表编码为LATIN1
LATIN1单字节编码,键长度320+3<767
CREATE TABLE (
TAG VARCHAR(320) NOT NULL,
DATE1 DATE,
ZIP SMALLINT,
DATA BLOB,
PRIMARY KEY(TAG,DATE1)
) ENGINE=InnoDB DEFAULT CHARSET=LATIN1;
既然是单字节编码,那距中文乱码的噩梦就不远了,幸好没有中文数据
方法三: 指定MyISAM
MyISAM可以容纳更大的主键长度为1000
CREATE TABLE (
TAG VARCHAR(320) NOT NULL,
DATE1 DATE,
ZIP SMALLINT,
DATA BLOB,
PRIMARY KEY(TAG,DATE1)
) ENGINE=MyISAM;
如果用了MyISAM引擎,就无法使用事务了,这对于大批量数据的INSERT,也是不可忍受的问题
二. mysql中文乱码
如果你经常用到mysql,数据库有中文数据,那么一定遇到过中文乱码的问题. mysql的编码太过复杂,可以设置数据库,表,表字段,客户端,服务器的编码.明明很简单的问题,搞的太复杂,像postgres就没这问题出现
1.客户端与服务器编码不一致
大多数是这种情况,这种情况还是好的,因为此时从服务器端过来数据是正常的,只是在客户端显示出现了问题
我们经常会看到有人查询时喜欢先执行: set names gbk; 然后查询中文数据,数据真的不乱码了.该语句是设置客户端编码为GBK,可是只对当前控制台有效,控制台重新打开后,又乱码了
可以通过编辑/etc/my.cnf使之永远有效:
[client]
default-character-set=utf8
重启mysql,乱码不见了
2.客户端编码与服务器数据库编码不一致
测试过程中,发现进程INSERT到数据库的中文都是乱码,直接打开mysql客户端手动执行INSERT竟然也是乱码,明显不认UTF8编码.查看编码方式发现mysql的sqlscada数据库为LATIN1编码,果断改成UTF8
alter database sqlscada character set utf8;
手动INSERT发现中文正常
3.总结
因为工作环境,经常要用到java和C++,java的字符串为UTF-8编码,为了使得各个语言上更好的工作,将数据库所有的编码设置为UTF-8
编辑/etc/my.cnf
[client]
default-character-set=utf8
[mysqld]
character-set-server=utf8
default-character-set=utf8
重启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/share/mysql/charsets/ |
+--------------------------+----------------------------+
自此再也没见mysql中文乱码
三. mysql blob大小
采样服务在postgres上工作一直很正常,突然将数据库迁移到mysql,发现blob数据总是被损坏,无法正常解压. 查看发现该blob大小很有特点为65535(0xFF)接近65K,blob数据大小为235k,难道被截断,查看mysql官方文档
原来mysql下有四种blob类型:
类型 大小(单位：字节)
TinyBlob 最大 255
Blob 最大 65K
MediumBlob 最大 16M
LongBlob 最大 4G
这mysql到处都是坑啊,postgres就一个bytea(变长字节序列)搞定一切二进制数据.更换成MediumBlob,问题解决
