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

Lennart Jonsson <erik.lennart.jonsson_at_gmail.com> writes:
>On 07/02/2015 07:15 AM, manoj_paramasivam_at_trimble.com wrote:
>[...]
>> i want restrict my text column 'b' to throw message on lenght greater than 32766.
>> But CHAR_LENGHT() is not working for such a large texts. here is my procedure. Any ideas?
>Is there a problem declaring the column that way?
>create table sample
>( a int not null
>, b varchar(32766) not null )
>You will get an error like:
>ERROR 1406 (22001): Data too long for column 'b' at row 1
>if the length of inserted value exceeds that of the column

  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.

  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?

  The following might work:

CREATE TABLE T ( N VARCHAR( 65533 ) NOT NULL ) ENGINE = MyISAM CHARACTER SET latin1;

  because it takes additional bytes required to store the   length into accout, does not use an engine that imposes   additional limitations, and restricts the character set to   latin1.

  When one measures the »length« os something, one should   read the documentation about what the functions count.   Characters? Code-Points? Octets? Or what else. Received on Sun Jul 05 2015 - 23:18:51 CEST