mysql Varchar size limit

views:

1097

answers:

+1 Q:

mysql Varchar size limit

Hi All,

Can anyone provide me a quick input on how mysql treat varchar data type size.

For Example: - If I have a column in table with field with Varchar(15) and if I try to insert data of length 16, then it gives an error stating

"Data too long for column 'testname' at row 1".

Does anyone know why varchar fields in mysql take fixed length. Also what is the fixed size of byte does a varchar field takes per record based on size given.

Thanks in advance.

Regards, Manasi

+3 A:

If you set a column to be varchar(15) the maximum bytes allowed is 15. Thus you can't pass it more than 15 characters without modifying the column to support more than 15. If you store a 4 character string it should only use around 4 bytes out of a possible 15, whereas if you used char(15) it would have filled in the other 11 with empty bytes.

http://dev.mysql.com/doc/refman/5.0/en/char.html

( My byte calculation was probably off since it's always -1/+1 or something like that ).

meder 2009-10-20 05:56:10

Thank you.Well in my case I m really uncertain what will be the length of data coming in. it could be long or could be small also. to be more precise:If I have specified column as Varchar(100) then the data can be of 100 characters or could be of 50 characters.Just to make my understanding clear. Do you mean it will act as CHAR data type only. I mean does it will take full 100 bytes for every record or only inserted 50 bytes. Also in other RDBMS's If I havent specify varchar limit or even after specifyin limit it does allow me to insert more characters than that.Is it possible in MYSQL.

MySQL DBA 2009-10-20 06:31:31

If you set it to `varchar(100)` and your row data is 50 characters then it will only use up half of that, it does *not* act as `char` which fills up the remaining bytes.

meder 2009-10-20 06:42:44

Thanks but it is not possible in insert more than 100 bytes in varchar(100) column.Sorry for asking the same question again and again. But i want to be very sure before setting any size to the column.Thanks again.

MySQL DBA 2009-10-20 06:56:56

The max for `varchar` is 255. If you are storing large amounts of data, try something like `TEXT` instead of `VARCHAR` then.

meder 2009-10-20 07:19:06

Varchar is 65,535 max. Char is 255 max. also I cannot use Text.

MySQL DBA 2009-10-20 08:23:07

I'm not sure why this is so complex. Just set a varchar to an amount you think it won't surpass.

meder 2009-10-20 08:35:02

well thats my problem. I have no clue what will be the size of the column. But thanks for your quick responses.

MySQL DBA 2009-10-20 11:27:29

+1 A:

If you look here it should tell you everything about varchar you want to know: http://dev.mysql.com/doc/refman/5.0/en/char.html

Basically, depending on the length you chose it will use 1 or two bytes to track the length of the current string in that column, so it will store the number of bytes for the data you put in, plus one or two bytes.

So, if you put in 'abc' then it will be 4 or 5 bytes used for that column in that row.

If you used char(15) then even 'abc' would take up 15 bytes, as the data is the right-padded to use up the full 15 bytes.

James Black 2009-10-20 06:03:11

Hi James,What do you mean by data is right-padded? Also do u mean that even if i entered 10 characters in varchar(15) column this will take whole 15 bytes for 10 characters like the Char(15) will take.

MySQL DBA 2009-10-20 06:38:56

No, the right padding is for char, not varchar.

James Black 2009-10-20 12:18:56

+2 A:

From the MySQL 5.0 Manual:

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

I only use VARCHAR when I'm certain that the data the column needs to hold will never exceed a certain length, and even then I'm cautious. If I'm storing a text string I tend to use one of the TEXT types.

Check out the MySQL Storage Requirements for more information on how the bytes are used.

mlambie 2009-10-20 06:04:36

+2 A:

Small extra local note. The number of bytes used will depend on the encoding scheme in use. 1 byte per character in latin1 encoding, but up to 3 in UTF8. See link in mlambie's answer for details.

martin clayton 2009-10-20 06:06:39

Thanks.yes. I'll be using UTF-8, SO it will take double byte.

MySQL DBA 2009-10-20 06:32:28

ansaurus

tags:

views:

answers:

mysql Varchar size limit

related questions