CODEWITHSUNDEEP
mysqlvarchar
BenMorel
BenMorel

Reputation: 36632

Why does MySQL VARCHAR allow more than the max length?

I have created a table with an UTF-8 VARCHAR(5000), and filled it with data. But it looks like this field is allowing more data than it is instructed to:

mysql> DESCRIBE test;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| comment | varchar(5000)    | YES  |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> SELECT MAX(LENGTH(comment)) FROM test;
+----------------------+
| MAX(LENGTH(comment)) |
+----------------------+
|                 5001 |
+----------------------+
1 row in set (0.01 sec)

Why is that?

Upvotes: 6

Views: 4596

Answers (4)

BenMorel
BenMorel

Reputation: 36632

Ok, the problem is that LENGTH() returns the length in bytes, not chars. Because the string is UTF-8, I need to use CHAR_LENGTH() instead:

mysql> DESCRIBE test;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| comment | varchar(5000)    | YES  |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> SELECT MAX(LENGTH(comment)) FROM test;
+----------------------+
| MAX(LENGTH(comment)) |
+----------------------+
|                 5001 |
+----------------------+
1 row in set (0.01 sec)

mysql> SELECT MAX(CHAR_LENGTH(comment)) FROM test;
+----------------------+
| MAX(LENGTH(comment)) |
+----------------------+
|                 5000 |
+----------------------+
1 row in set (0.01 sec)

The length was 5001 because the string contained exactly one two-byte character!

Upvotes: 9

Vikram Jain
Vikram Jain

Reputation: 5588

       The following table illustrates the differences between CHAR and VARCHAR
 by showing the result of storing various string values into CHAR(4) and
 VARCHAR(4) columns (assuming that the column uses a single-byte character
 set such as latin1).

    Value   |CHAR(4)    |Storage Required   |VARCHAR(4) |Storage Required
===================================================================================
    ''          '    '  4 bytes            ''           1 byte
    'ab'        'ab  '  4 bytes            'ab'         3 bytes
    'abcd'      'abcd'  4 bytes            'abcd'       5 bytes
    'abcdefgh'  'abcd'  4 bytes            'abcd'       5 bytes
===================================================================================


       The values shown as stored in the last row of the table apply only when
  not using strict mode; if MySQL is running in strict mode, values that exceed 
  the column length are not stored, and an error results.

Upvotes: 3

GregD
GregD

Reputation: 2867

The effective maximum length of a VARCHAR is 65,535 bytes. The number 5,000 you had created the VARCHAR column with, does not actually limit the length of the allowable storage for VARCHAR column. This is a different behavior as compared with CHAR data type.

11.4.1. The CHAR and VARCHAR Types

Upvotes: 1

ozhug
ozhug

Reputation: 1083

would it be that 5000 starts at zero, then counts on giving you 5001 chars. Does it do 5002 ?

Upvotes: 0

Related Questions