Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What happens when you store a value in a VARCHAR, which is over the limit in SQL? [closed]

Tags:

sql

mysql

varchar

If I setup a varchar(255) column will there be an error if I try to insert more than 255 characters or will it concatenate the inserted value to the first 255 characters?

like image 750
user756659 Avatar asked May 17 '14 15:05

user756659


People also ask

What is the limit for VARCHAR in SQL?

varchar [ ( n | max ) ] Variable-size string data. Use n to define the string size in bytes and can be a value from 1 through 8,000, or use max to indicate a column constraint size up to a maximum storage of 2^31-1 bytes (2 GB).

Does VARCHAR have a limit?

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

Does VARCHAR Max affect performance?

Yes, it can affect performance, as space needs to be allocated to hold large values in the query engine. In your case, you could also use a suitably large size such as varchar(50) which would easily hold whatever you needed.

Why should we avoid VARCHAR Max?

We can use the varchar(max) column as an included column in the index, but you cannot perform the index seek on this column. It will also require additional storage. Therefore, you should avoid creating an index with the varchar(max) data type.


1 Answers

It depends on server configuration:

If strict SQL mode is not enabled and you assign a value to a CHAR or VARCHAR column that exceeds the column's maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode.

More info here: The CHAR and VARCHAR Types

like image 109
Uriil Avatar answered Sep 24 '22 15:09

Uriil