MySQL Data Types VARCHAR(255) -- or not
Example
Suggested max len
First, I will mention some common strings that are always hex, or otherwise limited to ASCII. For these, you should specify CHARACTER SET ascii (latin1 is ok) so that it will not waste space:
UUID CHAR(36) CHARACTER SET ascii -- or pack into BINARY(16)
country_code CHAR(2) CHARACTER SET ascii
ip_address CHAR(39) CHARACTER SET ascii -- or pack into BINARY(16)
phone VARCHAR(20) CHARACTER SET ascii -- probably enough to handle extension
postal_code VARCHAR(20) CHARACTER SET ascii -- (not 'zip_code') (don't know the max
city VARCHAR(100) -- This Russian town needs 91:
Poselok Uchebnogo Khozyaystva Srednego Professionalno-Tekhnicheskoye Uchilishche Nomer Odin
country VARCHAR(50) -- probably enough
name VARCHAR(64) -- probably adequate; more than some government agencies allow
Why not simply 255? There are two reasons to avoid the common practice of using (255) for everything.
- When a complex
SELECTneeds to create temporary table (for a subquery,UNION,GROUP BY, etc), the preferred choice is to use theMEMORYengine, which puts the data in RAM. ButVARCHARsare turned intoCHARin the process. This makesVARCHAR(255) CHARACTER SET utf8mb4take 1020 bytes. That can lead to needing to spill to disk, which is slower. - In certain situations, InnoDB will look at the potential size of the columns in a table and decide that it will be too big, aborting a
CREATE TABLE.
VARCHAR versus TEXT
Usage hints for *TEXT, CHAR, and VARCHAR, plus some Best Practice:
- Never use
TINYTEXT. - Almost never use
CHAR-- it is fixed length; each character is the max length of theCHARACTER SET(eg, 4 bytes/character for utf8mb4). - With
CHAR, useCHARACTER SET asciiunless you know otherwise. VARCHAR(n)will truncate at n characters;TEXTwill truncate at some number of bytes. (But, do you want truncation?)*TEXTmay slow down complexSELECTsdue to how temp tables are handled.
