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
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

Temporal data

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

Index

⚠️ If the query has a function e.g. where YEAR(...)=2013; index does not help at all.

B-Tree (Balanced tree)