标签: varchar

mysql中处理未知大小的varchar

如果MySQL中varchar(M)M设置的太大,在磁盘上表占用空间大小只跟varchar中实际存放内容大小有关

但是如果查询要生成临时表,无论临时表是放在内存还是磁盘上,varchar都会扩张成M大小。这样将会消耗更多内存或者磁盘,对性能有严重的影响。

另外能用varchar就不要使用text,text性能很差,而且非常占磁盘空间。

varchar的长度是列共享的,一张表不要超过65535字节,对utf8编码约21485个字符

MySQL的varchar定义长度到底是字节还是字符

环境:CentOS5.x,MySQL5.1.x
UTF8字符集下:

[java]
SQL>create table test(id int auto_increment,name varchar(10),primary key(id));
SQL>insert into test values(null,’1234567890′);
Query OK, 1 row affected (0.00 sec)
SQL>insert into test values(null,’一二三四五六七八九十’);
Query OK, 1 row affected (0.00 sec)
SQL>insert into test values(null,’abcdefghig’);
Query OK, 1 row affected (0.01 sec)
SQL>insert into test values(null,12345678901);
ERROR 1406 (22001): Data too long for column ‘name’ at row 1
SQL>insert into test values(null,’一二三四五六七八九十1′);
ERROR 1406 (22001): Data too long for column ‘name’ at row 1
SQL>insert into test values(null,’一二三四五六七八九十一’);
ERROR 1406 (22001): Data too long for column ‘name’ at row 1
SQL>select id,name,length(name),char_length(name) from test;

[/java]

阅读详细 »