CODEWITHSUNDEEP
mysql
SST
SST

Reputation: 2144

What is the storage size of VARCHAR(255) in MySQL

I am able to store more than 255 characters (say 'a') in a MySQL column whose data type is VARCHAR(255). Kindly clarify

Upvotes: 0

Views: 651

Answers (1)

Trent Lloyd
Trent Lloyd

Reputation: 1892

The maximum storage size of a character column is defined by the width (i.e. 255). You can insert shorter values, but they will be truncated and present a warning.

If you use one of the strict SQL modes, it will produce an error instead of a warning.

See documentation: https://dev.mysql.com/doc/refman/5.6/en/string-type-overview.html

mysql [localhost] {msandbox} (test) > CREATE TABLE t_varchar(a VARCHAR(127));
Query OK, 0 rows affected (0.02 sec)

mysql [localhost] {msandbox} (test) > SET sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO t_varchar VALUES(REPEAT('a', 127));
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO t_varchar VALUES(REPEAT('a', 128));
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql [localhost] {msandbox} (test) > SELECT LENGTH(a) FROM t_varchar;
+-----------+
| LENGTH(a) |
+-----------+
|       127 |
|       127 |
+-----------+
2 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > 
mysql [localhost] {msandbox} (test) > TRUNCATE TABLE t_varchar;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > SET sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO t_varchar VALUES(REPEAT('a', 127));
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO t_varchar VALUES(REPEAT('a', 128));
ERROR 1406 (22001): Data too long for column 'a' at row 1
mysql [localhost] {msandbox} (test) > SELECT LENGTH(a) FROM t_varchar;
+-----------+
| LENGTH(a) |
+-----------+
|       127 |
+-----------+
1 row in set (0.00 sec)

Upvotes: 1

Related Questions