Specified key was too long; max key length is 767

公司开发的应用,一般要兼容大多数主流数据库,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,问题解决