SQL
SQL joins
MySQL data types
Character Data
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
Character sets
SHOW CHARACTER SET;
some character sets may be multibyte (several bytes to store one character)
Text data
text type | max byte size |
tinytext | 255 |
text | 65KB |
mediumtext | 16MB |
longtext | 4GB |
if data longer than column size => data is truncated
Numeric data
Type | Signed range | Unsigned range |
| −128 to 127 | 0 to 255 |
| −32,768 to 32,767 | 0 to 65,535 |
| −8,388,608 to 8,388,607 | 0 to 16,777,215 |
| −2,147,483,648 to 2,147,483,647 | 0 to 4,294,967,295 |
| −2^63 to 2^63 - 1 | 0 to 2^64 - 1 |
Type | Numeric range |
| −3.402823466E+38 to −1.175494351E-38 and 1.175494351E-38 to 3.402823466E+38 |
| −1.7976931348623157E+308 to −2.2250738585072014E-308 and 2.2250738585072014E-308 to 1.7976931348623157E+308 |
Temporal data
Type | Default format | Allowable values |
| YYYY-MM-DD |
|
| YYYY-MM-DD HH:MI:SS |
to |
| YYYY-MM-DD HH:MI:SS |
to |
| YYYY |
|
| HHH:MI:SS |
to |
Index
⚠️ If the query has a function e.g. where YEAR(...)=2013;
index does not help at all.
B-Tree (Balanced tree)
Last updated