SAS/ACCESS Interface for MySQL

Data Types for MySQL

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. Variables entered into columns of this type must be inserted 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): 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 INT can range from -2147483648 to 2147483647.
MEDIUMINT (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 MEDIUMINT can range from -8388608 to 8388607.
SMALLINT (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 SMALLINT can range from -32768 to 32767.
TINYINT (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 TINYINT can range from -128 to 127.

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--for example, 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

The following 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.n*	DECIMAL ([m-1],n)**
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
* n in MySQL data types is equivalent to w in SAS formats. ** DECIMAL 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.

Top of Page