MySQL - Reference and Migration

Technical information on MySQL and its conversion from/to other databases.

Last Update: MySQL 5.6

Language Elements

SQL language elements:

Single Line Comment # -- Until the end of line, -- must be followed by a space or tab

Identifiers

Names for database objects such as databases, tables, views, columns, indexes, triggers, procedures etc.

First character Alphanumeric
Subsequent characters Alphanumeric, _ (underscore) and $ (dollar)
Identifier quote character ` (backtick) " (double quotes) if ANSI_QUOTES mode is set (not set by default)

Maximum length:

Databases, tables, columns, indexes, views, procedures, triggers, constraints 64

Data Types

MySQL data types:

1 BIGINT 64-bit integer
2 BINARY(n) 0 <= n <= 255 Fixed-length binary data
3 CHAR(n) Fixed-length string
4 DATE Date (year, month and day)
5 DATETIME(p) Datetime (year, month, day, hour, minute, second and microseconds)
6 DECIMAL(p,s) Fixed-point number
7 INT, INTEGER 32-bit integer
8 LONGTEXT 4 GB Character large object (LOB)
9 MEDIUMTEXT 16 MB Character large object (LOB)
10 NVARCHAR(n) 0 <= n <= 65535 Variable-length string
11 TEXT 64 KB Character large object (LOB)
12 TINYINT -128 to 127 8-bit integer Unsigned: 0 to 255
13 TIMESTAMP(p) Auto-updated datetime
14 VARCHAR(n) 0 <= n <= 65535 Variable-length string

Note In MySQL, the maximum row size 65,535, so the maximum length of string columns (except LOB and TEXT columns) is limited by other columns in the table.

Database Schema and Data

SQL Language

How To - Most Popular Data Definition (DDL) Tasks

How To - Most Popular Queries and Expressions

Tables - DDL Options

Built-in Functions

Statements