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

From: Stefan Ram <ram_at_zedat.fu-berlin.de>
Date: 6 Jul 2015 22:12:14 GMT
Message-ID: <MySQL-20150707001048_at_ram.dialup.fu-berlin.de>

Lennart Jonsson <erik.lennart.jonsson_at_gmail.com> writes:

>On 07/05/2015 11:18 PM, Stefan Ram wrote:
>>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:

  Possibly I made an experience with an older version of   MySQL or some special settings, but it happened after 2012.   I now see that I can use, for example, »VARCHAR ( 2550 )«   with a recent version of MySQL.

  It is still true that one must be careful not to exceed   the overall size for a row.

mysql> CREATE TABLE T0


    -> ( C0 VARCHAR ( 2550 ),

    ->   C1 VARCHAR ( 2550 ),

    ->   C2 VARCHAR ( 2550 ),

    ->   C3 VARCHAR ( 2550 ),

    ->   C4 VARCHAR ( 2550 ),

    ->   C5 VARCHAR ( 2550 ),

    ->   C6 VARCHAR ( 2550 ),

    ->   C7 VARCHAR ( 2550 ),

-> C8 VARCHAR ( 2550 ),
-> C9 VARCHAR ( 2550 ));

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

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

mysql> CREATE TABLE T ( N VARCHAR( 65533 ) NOT NULL )

-> ENGINE = MyISAM CHARACTER SET latin1;
Query OK, 0 rows affected (0.00 sec) Received on Tue Jul 07 2015 - 00:12:14 CEST