Re: mysql lenght() and char_length not working for longer texts

On 07/05/2015 11:18 PM, Stefan Ram wrote: [...]
> I vaguely remember that a text in a database might contain
> Unicode characters and that this might need several octets
> per character. So when a column can have up to 32766 octets,
> this does not necessarily mean 32766 characters.
>

If you store unicode characters in a column you better use a character set that supports it, like utf-8

create table t (x varchar(3) not null)
engine = innodb character set utf8;

å,ä, and ö are two byte characters in utf-8

insert into t (x) values ('åäö');

MariaDB [test]> select * from t;
+--------+
| x |
+--------+
| åäö |
+--------+

> But I remember that I found out that effectively varchar
> seems to be limited to about 255 characters here with
> standard settings of MySQL. I don't really understand fully why.
> Possibly some restriction of InnoDB?
>

Not sure what you are refering to here:

create table t (x varchar(1000) not null) engine = innodb; create table tmp ( a varchar(10) not null ) engine = innodb; insert into tmp values
('abcdefgåäö'),('abcdefgåäö'),('abcdefgåäö'),('abcdefgåäö'),('abcdefgåäö'),('abcdefgåäö'),('abcdefgåäö'),('abcdefgåäö'),('abcdefgåäö'),('abcdefgåäö'); insert into t(x) select substr(group_concat(a.a),1,1000) from tmp a, tmp b; select * from t;  

abcdefgåäö,abcdefgåäö,abcdefgåäö,abcdefgåäö,abcdefgåäö,abcdefgåäö,abcdef...

> The following might work:
>
> CREATE TABLE T ( N VARCHAR( 65533 ) NOT NULL )
> ENGINE = MyISAM CHARACTER SET latin1;
>

Assuming this is true, there are characters that are represented with more than two bytes so just doubling the space wont suffice, see for example g-clef which is 4 bytes:

http://www.fileformat.info/info/unicode/char/1d11e/index.htm

But beside that, the whole point with the length restriction would be gone since you could store twice the amount of one byte characters.

/Lennart Received on Mon Jul 06 2015 - 23:33:38 CEST