SQL
Last updated
Last updated
char(20)
fixed length
255 bytes max
string in the column have the same length
varchar(20)
variable length
64 KB max
strings may have diff length in the column
SHOW CHARACTER SET;
some character sets may be multibyte (several bytes to store one character)
text type
max byte size
tinytext
255
text
65KB
mediumtext
16MB
longtext
4GB
if data longer than column size => data is truncated
Type
Signed range
Unsigned range
tinyint
−128 to 127
0 to 255
smallint
−32,768 to 32,767
0 to 65,535
mediumint
−8,388,608 to 8,388,607
0 to 16,777,215
int
−2,147,483,648 to 2,147,483,647
0 to 4,294,967,295
bigint
−2^63 to 2^63 - 1
0 to 2^64 - 1
Type
Numeric range
float(
p
,
s
)
−3.402823466E+38 to −1.175494351E-38
and 1.175494351E-38 to 3.402823466E+38
double(
p
,
s
)
−1.7976931348623157E+308 to −2.2250738585072014E-308
and 2.2250738585072014E-308 to 1.7976931348623157E+308
Type
Default format
Allowable values
date
YYYY-MM-DD
1000-01-01
to 9999-12-31
datetime
YYYY-MM-DD HH:MI:SS
1000-01-01 00:00:00.000000
to 9999-12-31 23:59:59.999999
timestamp
YYYY-MM-DD HH:MI:SS
1970-01-01 00:00:00.000000
to 2038-01-18 22:14:07.999999
year
YYYY
1901
to 2155
time
HHH:MI:SS
−838:59:59.000000
to 838:59:59.000000
⚠️ If the query has a function e.g. where YEAR(...)=2013;
index does not help at all.