CODEWITHSUNDEEP
phpmysqltruncate
Alaa
Alaa

Reputation: 4611

Text datatype in mysql is truncated at 32k


I have php script that inserts articles in mysql DB. the field type in mysql is text.
now, when i insert an article larger than 32K, it is truncated to 32K only. what i know is the max size of text in mysql is 64K.
PS.: mysql version is 5.0.51a-24+lenny5
PHP version is: PHP 5.3.2-1ubuntu4.9
mysql: max_allowed_packet=16M

any idea of why does mysql truncate it or how to fix it??
** EDIT **
my character set is utf8
by selecting hex of this field i got 65768, and as you know every two hex digits represent one byte, and thus here the actual size is 65768/2=32884 

mysql> select length(hex(body)), length(body) from articles  where article_id=62727;
+-------------------+--------------+
| length(hex(body)) | length(body) |
+-------------------+--------------+
|             65768 |        32884 | 
+-------------------+--------------+

Thanks for your help

Upvotes: 2

Views: 1651

Answers (2)

joe776
joe776

Reputation: 1116

From: http://dev.mysql.com/doc/refman/5.0/en/string-type-overview.html

A TEXT column with a maximum length of 65,535 (2^16 – 1) characters. The effective maximum length is less if the value contains multi-byte characters.

So the maximum length of 64k characters is only possible with a charset like ANSI. UTF-8, for example, uses more than one byte to encode a character and thus less text can be stored in 2^16 bytes.

Upvotes: 1

Danilo Tommasina
Danilo Tommasina

Reputation: 1760

The TEXT type has a length of 64k Bytes and not Characters, so if you use a character set using more than one byte per character, then this error may occur.

In your case the string is always truncated at 32k, it looks like that you are using a UTF-16 character set which requires two bytes per character.

You have two possibilities:

  1. Use a single-byte character set
  2. Use a larger column type

Upvotes: 7

Related Questions