Previous Page | Next Page

SAS/ACCESS Interface for MySQL

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

Previous Page | Next Page | Top of Page