Data Types

When a table is created, a data type is declared for each column. MySQL groups its column data types into numeric, string, date and time, and spatial families.

Numeric

Integer types differ in storage size and range:

BOOL, BOOLEAN – 1 byte – 0 to 1

TINYINT – 1 byte – -128 to 127

SMALLINT – 2 bytes – -32768 to 32767

MEDIUMINT – 3 bytes – -8388608 to 8388607

INT, INTEGER – 4 bytes – -2147483648 to 2147483647

BIGINT – 8 bytes – -9223372036854775808 to 9223372036854775807

Ranges and storage sizes for the MySQL integer types.

BOOL and BOOLEAN are synonyms for TINYINT(1).

An UNSIGNED attribute can be appended to an integer type so that only non-negative integers are stored; this raises the upper limit of the type. For instance, TINYINT UNSIGNED stores integers from 0 to 255.

A bracketed integer can be appended to an integer type to set the display width. For example, for the type TINYINT(5), a value of 10 is left-padded with three spaces when displayed.

DECIMAL(8,3) stores exact values from -99999.999 to 99999.999, where the precision is 8 digits and the scale is 3. The number of bytes used varies with precision. DECIMAL(10) is the same as DECIMAL(10,0); DECIMAL alone is the same as DECIMAL(10) in MySQL; DECIMAL(8,3) is the same as NUMERIC(8,3).

FLOAT, REAL, DOUBLE, and DOUBLE PRECISION store approximate numeric values. REAL, DOUBLE, and DOUBLE PRECISION are the same. In MySQL, single-precision values use 4 bytes while double-precision values use 8 bytes. UNSIGNED can also be appended to these types to prevent negative values, though the upper limit of the range is not increased.

BIT(10) stores values of 10 bits. A binary representation is specified using a notation like b'1001010101'. The default is 1 bit when the parenthesized length is omitted.

String

CHAR(10) holds up to 10 characters, and always uses 10 bytes regardless of how long the actual string is. VARCHAR(10) holds up to 10 characters, using the length of the string plus one or two bytes.

BINARY(10) and VARBINARY(10) are similar to CHAR(10) and VARCHAR(10), except they contain byte strings rather than character strings. Without a character set, BINARY(n) and VARBINARY(n) sort and compare based on the numeric values of the bytes. CHAR(10) BINARY and VARCHAR(10) BINARY use the binary collation for the column's character set.

TINYBLOB, TINYTEXT – L+1 bytes, L < 2^8

BLOB, TEXT – L+2 bytes, L < 2^16

MEDIUMBLOB, MEDIUMTEXT – L+3 bytes, L < 2^24

LONGBLOB, LONGTEXT – L+4 bytes, L < 2^32

Storage required for the BLOB/TEXT family, where L is the number of bytes in the string.

BLOB values are byte strings (binary data); they use the binary character set and collation, and comparison/sorting is based on the numeric byte values. TEXT values are character strings (textual data) that use a character set other than binary, and are sorted and compared based on the character set's collation.

ENUM('cat','dog') can contain the values NULL, '', 'cat', and 'dog'. Each predefined value equals an integer, starting with 1; 0 is reserved for the empty string. Using ENUM() saves storage space. SET('cat','dog') can contain the values NULL, '', 'cat', 'dog', and 'cat,dog'; multiple members are separated by commas, and a maximum of 64 members is allowed. In this example 'cat' has a binary value of 01 while 'dog' has a binary value of 10.

The JSON type allows JSON (JavaScript Object Notation) strings to be manipulated directly with functions such as JSON_SET() and JSON_REMOVE(), and with the -> and ->> operators (the latter returning an unquoted value). See the worked example below.

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer VARCHAR(50),
    items JSON
);

INSERT INTO orders (customer, items) VALUES
   ('John', '[{"name": "item1", "price": 10}, {"name": "item2", "price": 20}]'),
   ('Jane', '[{"name": "item3", "price": 30}, {"name": "item4", "price": 40}]');

UPDATE orders SET items = JSON_SET(items, '$[0].price', 15) WHERE customer = 'John';
UPDATE orders SET items = JSON_REMOVE(items, '$[1]') WHERE customer = 'Jane';

SELECT items->'$[1].price' FROM orders WHERE customer = 'John';
SELECT items->>'$[0].name' FROM orders WHERE customer = 'Jane'; -- unquoted

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer VARCHAR(50),
    items JSON
);

INSERT INTO orders (customer, items) VALUES
   ('John', '[{"name": "item1", "price": 10}, {"name": "item2", "price": 20}]'),
   ('Jane', '[{"name": "item3", "price": 30}, {"name": "item4", "price": 40}]');

UPDATE orders SET items = JSON_SET(items, '$[0].price', 15) WHERE customer = 'John';
UPDATE orders SET items = JSON_REMOVE(items, '$[1]') WHERE customer = 'Jane';

SELECT items->'$[1].price' FROM orders WHERE customer = 'John';
SELECT items->>'$[0].name' FROM orders WHERE customer = 'Jane'; -- unquoted

Query OK, 1 row affected Query OK, 1 row affected

items->'$[1].price'
20

items->>'$[0].name'
item3

Backticks are used for table and column identifiers, but are only necessary when the identifier is a MySQL reserved keyword, or when it contains whitespace or characters outside a limited allowed set. It is often recommended to avoid reserved keywords as identifiers to sidestep the quoting issue, e.g. CREATE TABLE `My Table`(a int);

Date and Time

DATE – '1000-01-01' to '9999-12-31'

TIME – '-838:59:59.000000' to '838:59:59.999999'

YEAR – 1 byte, display width 4, range 1901-2155

DATETIME – '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'

TIMESTAMP – '1000-01-01 00:00:00.000000' UTC to '9999-12-31 23:59:59.999999' UTC

Ranges for the date and time types.

For TIME, '11:30' means '11:30:00' while '1130' means '00:11:30'. YEAR may be specified as an integer or a string.

TIMESTAMP values are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval. A time zone offset can be appended to DATETIME and TIMESTAMP literals, e.g. 2019-12-11 10:40:30-05:00, 2003-04-14 03:30:00+10:00, 2020-01-01 15:35:45+05:30. If two-digit year values are used, 00-69 are converted to 2000-2069, and 70-99 are converted to 1970-1999.

For DATETIME and TIMESTAMP, use DEFAULT to set an initial default value – e.g. dt DATETIME DEFAULT CURRENT_TIMESTAMP initializes the column to the current timestamp whenever no value is supplied on insert. Use ON UPDATE to auto-refresh the value – e.g. dt TIMESTAMP ON UPDATE CURRENT_TIMESTAMP sets the column to the current timestamp whenever another column in the row changes.

SELECT
    NOW(),
    CAST(NOW() AS DATE),
    CAST(NOW() AS TIME),
    CURRENT_DATE,
    CURRENT_TIME;

SELECT
    NOW(),
    CAST(NOW() AS DATE),
    CAST(NOW() AS TIME),
    CURRENT_DATE,
    CURRENT_TIME;

Spatial

MySQL 8 accepts the following geometry data types: [MULTI]POINT, [MULTI]LINESTRING, [MULTI]POLYGON, and GEOMETRY[COLLECTION].

The POINT data type efficiently processes geographic coordinates. Functions such as ST_AsText() render a geometry value as WKT text, ST_Distance_Sphere() computes the great-circle distance between two points, and ST_GeomFromText() parses WKT into a geometry value (used for MULTIPOLYGON below). A SPATIAL INDEX can be declared on a spatial column to accelerate these queries. See the worked example for finding the nearest location to a given point and for summing the area of a set of multipolygons with ST_Area().

CREATE TABLE locations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    coordinates POINT NOT NULL,
    SPATIAL INDEX idx_coordinates (coordinates)
);

INSERT INTO locations (name, coordinates) VALUES
   ('New York', POINT(-74.005941, 40.712784)),
   ('Los Angeles', POINT(-118.243685, 34.052234)),
   ('Chicago', POINT(-87.629798, 41.878114)),
   ('Houston', POINT(-95.369803, 29.760427));

CREATE TABLE locations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    coordinates POINT NOT NULL,
    SPATIAL INDEX idx_coordinates (coordinates)
);

INSERT INTO locations (name, coordinates) VALUES
   ('New York', POINT(-74.005941, 40.712784)),
   ('Los Angeles', POINT(-118.243685, 34.052234)),
   ('Chicago', POINT(-87.629798, 41.878114)),
   ('Houston', POINT(-95.369803, 29.760427));

SELECT name, ST_AsText(coordinates) FROM locations;

SELECT name, ST_AsText(coordinates) FROM locations;

name	ST_AsText(coordinates)
New York	POINT(-74.005941 40.712784)
Los Angeles	POINT(-118.243685 34.052234)
Chicago	POINT(-87.629798 41.878114)
Houston	POINT(-95.369803 29.760427)

-- distance in meters between every pair of locations
SELECT
    locations.name AS location1,
    other_locations.name AS location2,
    ST_Distance_Sphere(locations.coordinates, other_locations.coordinates) AS distance
FROM locations CROSS JOIN locations AS other_locations
WHERE locations.id < other_locations.id;

-- distance in meters between every pair of locations
SELECT
    locations.name AS location1,
    other_locations.name AS location2,
    ST_Distance_Sphere(locations.coordinates, other_locations.coordinates) AS distance
FROM locations CROSS JOIN locations AS other_locations
WHERE locations.id < other_locations.id;

location1	location2	distance
New York	Los Angeles	3937989.33079
New York	Chicago	1142097.66558
New York	Houston	2162347.90358
Los Angeles	Chicago	2818852.53463
Los Angeles	Houston	1971969.98913
Chicago	Houston	1503334.9016

-- nearest location to a given point
SELECT name, ST_Distance_Sphere(coordinates, POINT(-72.005941, 41.712784)) AS distance
FROM locations
ORDER BY distance
LIMIT 1;

-- nearest location to a given point
SELECT name, ST_Distance_Sphere(coordinates, POINT(-72.005941, 41.712784)) AS distance
FROM locations
ORDER BY distance
LIMIT 1;

name	distance
New York	200870.00435944612

CREATE TABLE my_multipolygons (
  id INT AUTO_INCREMENT PRIMARY KEY,
  mp MULTIPOLYGON
);

INSERT INTO my_multipolygons (mp) VALUES
  (ST_GeomFromText('MULTIPOLYGON (((0 0, 0 1, 1 1, 1 0, 0 0)), ((2 2, 2 3, 3 3, 3 2, 2 2)))')),
  (ST_GeomFromText('MULTIPOLYGON (((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)), ((-3 -3, -3 -4, -4 -4, -4 -3, -3 -3)))'));

CREATE TABLE my_multipolygons (
  id INT AUTO_INCREMENT PRIMARY KEY,
  mp MULTIPOLYGON
);

INSERT INTO my_multipolygons (mp) VALUES
  (ST_GeomFromText('MULTIPOLYGON (((0 0, 0 1, 1 1, 1 0, 0 0)), ((2 2, 2 3, 3 3, 3 2, 2 2)))')),
  (ST_GeomFromText('MULTIPOLYGON (((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)), ((-3 -3, -3 -4, -4 -4, -4 -3, -3 -3)))'));

SELECT SUM(ST_Area(mp)) AS total_area FROM my_multipolygons;

SELECT SUM(ST_Area(mp)) AS total_area FROM my_multipolygons;

total_area
4

Vector

MySQL 9.0 adds a VECTOR data type aimed at AI, embedding, and similarity-search workloads: a column that stores a fixed-length array of 4-byte single-precision floating-point values. VECTOR(N) declares a column holding up to N entries; N defaults to 2048 and can go up to 16383. A VECTOR value can only be compared to another VECTOR for equality, cannot be used as any kind of key (PRIMARY, FOREIGN, UNIQUE, or partitioning), and supports only a small subset of functions – string functions such as HEX() and LENGTH(), a few encryption functions, and the COUNT() aggregate; ordinary numeric, temporal, and JSON functions do not apply to it.

STRING_TO_VECTOR() converts a string such as '[1.05,-17.8,32]' into a VECTOR column's internal binary representation, and VECTOR_TO_STRING() converts that binary representation back into a readable string; VECTOR_DIM() returns the number of entries a vector holds. These functions, and the VECTOR type itself, are included in MySQL Community Edition.

Community Edition does not, however, include any built-in distance/similarity function or nearest-neighbor index for VECTOR columns – the DISTANCE() function (which computes a COSINE, DOT, or EUCLIDEAN distance between two vectors) and the approximate nearest-neighbor VECTOR INDEX, built on the HNSW (Hierarchical Navigable Small World) algorithm for fast large-scale similarity search, are both capabilities of HeatWave (Oracle's paid cloud database service, also marketed as MySQL AI) – neither ships in the Commercial or Community server distributions. A self-hosted community server can still store, round-trip, and inspect vectors with the functions above; computing an exact nearest-neighbor distance between them requires either application code or a self-hosted extension, not a built-in server function. See the worked example below.

-- VECTOR type, storage, and conversion functions (MySQL 9.0+, Community Edition)
CREATE TABLE embeddings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    label VARCHAR(50),
    embedding VECTOR(4)
);

INSERT INTO embeddings (label, embedding) VALUES
   ('cat photo',   STRING_TO_VECTOR('[0.12, 0.98, -0.34, 0.05]')),
   ('dog photo',   STRING_TO_VECTOR('[0.10, 0.95, -0.30, 0.02]')),
   ('car photo',   STRING_TO_VECTOR('[-0.88, 0.02, 0.41, 0.77]'));

SELECT label, VECTOR_TO_STRING(embedding), VECTOR_DIM(embedding)
FROM embeddings;

-- DISTANCE() and an HNSW-backed VECTOR INDEX for approximate nearest-neighbor
-- search are HeatWave-only; they are not available on this Community server:
-- SELECT label FROM embeddings
--     ORDER BY DISTANCE(embedding, STRING_TO_VECTOR('[0.11,0.96,-0.32,0.03]'), 'COSINE')
--     LIMIT 1; -- requires MySQL HeatWave / MySQL AI, not Community Edition

-- VECTOR type, storage, and conversion functions (MySQL 9.0+, Community Edition)
CREATE TABLE embeddings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    label VARCHAR(50),
    embedding VECTOR(4)
);

INSERT INTO embeddings (label, embedding) VALUES
   ('cat photo',   STRING_TO_VECTOR('[0.12, 0.98, -0.34, 0.05]')),
   ('dog photo',   STRING_TO_VECTOR('[0.10, 0.95, -0.30, 0.02]')),
   ('car photo',   STRING_TO_VECTOR('[-0.88, 0.02, 0.41, 0.77]'));

SELECT label, VECTOR_TO_STRING(embedding), VECTOR_DIM(embedding)
FROM embeddings;

-- DISTANCE() and an HNSW-backed VECTOR INDEX for approximate nearest-neighbor
-- search are HeatWave-only; they are not available on this Community server:
-- SELECT label FROM embeddings
--     ORDER BY DISTANCE(embedding, STRING_TO_VECTOR('[0.11,0.96,-0.32,0.03]'), 'COSINE')
--     LIMIT 1; -- requires MySQL HeatWave / MySQL AI, not Community Edition

MENU