Menu
I have a table with about 2 million records in it. I have a field called message which is setup as varchar(160) and I need something bigger that varchar(255) because I need to be able to store about 500 characters in it, what is the next best datatype to use without increasing the size of the db drastically?
thanks
252
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. Make sure you are aware of the effects of a multi-byte character set. VARCHAR(255) stores 255 characters, which may be more than 255 bytes.
This means, that in a varchar column for each row one additional byte is used to store how long the string in the row actually is. So the difference between varchar(1024) and varchar(512) is, that your data gets truncated when you try to insert more than 1024 or 512 bytes. Note: bytes, not characters.
One should use Varchar over Char if your input is going to be varying lengths. Like a name field.. Set it to what you believe will be the max length.. SQL will only allocate what you use, as opposed to char which will always allocate the max needed.
In most circumstances, VARCHAR provides better performance, it's more flexible, and can be fully indexed. If you need to store longer strings, use MEDIUMTEXT or LONGTEXT, but be aware that very large amounts of data can be stored in columns of these types.
If you're on 5.0.3 or later a varchar can be 65535 characters.
11.4.1. The CHAR and VARCHAR Types
MySQL: Large VARCHAR vs. TEXT
41
answered Sep 24 '22 22:09
Starting with MySQL 5.0.3, you can simply use VARCHAR(500).
M represents the maximum column length in characters. In MySQL 5.0, the range of M is 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in MySQL 5.0.3 and later.
166
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
