Data Types for MySQL

Overview

Every column in a table has a name and a data type. The data type tells MySQL how much physical storage to set aside for the column and the form in which the data is stored. This section includes information about MySQL data types and data conversions.

Character Data

BLOB (binary large object)

contains binary data of variable length up to 64 kilobytes. When you enter variables of this type into columns, you must insert them as character strings.

CHAR (n)

contains fixed-length character string data with a length of n, where n must be at least 1 and cannot exceed 255 characters.

ENUM (“value1”, “value2”, “value3”,…)

contains a character value that can be chosen from the list of allowed values. You can specify up to 65535 ENUM values. If the column contains a string not specified in the value list, the column value is set to “0”.

LONGBLOB

contains binary data of variable length up to 4 gigabytes. Variables entered into columns of this type must be inserted as character strings. Available memory considerations might limit the size of a LONGBLOB data type.

LONGTEXT

contains text data of variable length up to 4 gigabytes. Available memory considerations might limit the size of a LONGTEXT data type.

MEDIUMBLOB

contains binary data of variable length up to 16 megabytes. Variables entered into columns of this type must be inserted as character strings.

MEDIUMTEXT

contains text data of variable length up to 16 megabytes.

SET (“value1”, “value2”, “value3”,…)

contains zero or more character values that must be chosen from the list of allowed values. You can specify up to 64 SET values.

TEXT

contains text data of variable length up to 64 kilobytes.

TINYBLOB

contains binary data of variable length up to 256 bytes. Variables entered into columns of this type must be inserted as character strings.

TINYTEXT

contains text data of variable length up to 256 bytes.

VARCHAR (n)

contains character string data with a length of n, where n is a value from 1 to 255.

Numeric Data

BIGINT (n)

specifies an integer value, where n indicates the display width for the data. You might experience problems with MySQL if the data column contains values that are larger than the value of n. Values for BIGINT can range from –9223372036854775808 to 9223372036854775808.

DECIMAL (length, decimals)

specifies a fixed-point decimal number, where length is the total number of digits (precision), and decimals is the number of digits to the right of the decimal point (scale).

DOUBLE (length, decimals)

specifies a double-precision decimal number, where length is the total number of digits (precision), and decimals is the number of digits to the right of the decimal point (scale). Values can range from approximately –1.8E308 to –2.2E-308 and 2.2E-308 to 1.8E308 (if UNSIGNED is specified).

FLOAT (length, decimals)

specifies a floating-point decimal number, where length is the total number of digits (precision) and decimals is the number of digits to the right of the decimal point (scale). Values can range from approximately –3.4E38 to –1.17E-38 and 1.17E-38 to 3.4E38 (if UNSIGNED is specified).

INT (n)

MEDIUMINT (n)

SMALLINT (n)

TINYINT (n)

Date, Time, and Timestamp Data

DATE

contains date values. Valid dates are from January 1, 1000, to December 31, 9999. The default format is YYYY-MM-DD. An example is 1961-06-13.

DATETIME

contains date and time values. Valid values are from 00:00:00 on January 1, 1000, to 23:59:59 on December 31, 9999. The default format is YYYY-MM-DD HH:MM:SS (for example, 1992-09-20 18:20:27).

TIME

contains time values. Valid times are –838 hours, 59 minutes, 59 seconds to 838 hours, 59 minutes, 59 seconds. The default format is HH:MM:SS (for example, 12:17:23).

TIMESTAMP

contains date and time values used to mark data operations. Valid values are from 00:00:00 on January 1, 1970, to 2037. The default format is YYYY-MM-DD HH:MM:SS (for example, 1995–08–09 15:12:27).

LIBNAME Statement Data Conversions

This table shows the default formats that SAS/ACCESS Interface to MySQL assigns to SAS variables when using the LIBNAME statement to read from a MySQL table. These default formats are based on MySQL column attributes.

LIBNAME Statement: Default SAS Formats for MySQL Data Types

MySQL Column Type	SAS Data Type	Default SAS Format
CHAR(n )	character	$n.
VARCHAR(n )	character	$n.
TINYTEXT	character	$n.
TEXT	character	$n. (where n is the value of the DBMAX_TEXT= option)
MEDIUMTEXT	character	$n. (where n is the value of the DBMAX_TEXT= option)
LONGTEXT	character	$n. (where n is the value of the DBMAX_TEXT= option)
TINYBLOB	character	$n. (where n is the value of the DBMAX_TEXT= option)
BLOB	character	$n. (where n is the value of the DBMAX_TEXT= option)
MEDIUMBLOB	character	$n. (where n is the value of the DBMAX_TEXT= option)
LONGBLOB	character	$n. (where n is the value of the DBMAX_TEXT= option)
ENUM	character	$n.
SET	character	$n.
TINYINT	numeric	4.0
SMALLINT	numeric	6.0
MEDIUMINT	numeric	8.0
INT	numeric	11.0
BIGINT	numeric	20.
DECIMAL	numeric	m.n
FLOAT	numeric
DOUBLE	numeric
DATE	numeric	DATE
TIME	numeric	TIME
DATETIME	numeric	DATETIME
TIMESTAMP	numeric	DATETIME

This table shows the default MySQL data types that SAS/ACCESS assigns to SAS variable formats during output operations when you use the LIBNAME statement.

LIBNAME Statement: Default MySQL Data Types for SAS Variable Formats

SAS Variable Format	MySQL Data Type
m.n1	DECIMAL ([m-1],n)2
n (where n <= 2)	TINYINT
n (where n <= 4)	SMALLINT
n (where n <=6)	MEDIUMINT
n (where n <= 17)	BIGINT
other numerics	DOUBLE
$n (where n <= 255)	VARCHAR(n)
$n (where n > 255)	TEXT
datetime formats	TIMESTAMP
date formats	DATE
time formats	TIME
1n in MySQL data types is equivalent to w in SAS formats.
2DECIMAL types are created as (m-1, n). SAS includes space to write the value, the decimal point, and a minus sign (if necessary) in its calculation for precision. These must be removed when converting to MySQL.