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.

Supported MySQL Data Types

Here are the data types that the MySQL engine supports.

• Character data:

  BLOB (binary large object)	MEDIUMTEXT
  CHAR (n)	SET ("value1", "value2", "value3",…)
  ENUM ("value1", "value2", "value3",…)	TEXT
  JSON	TINYBLOB
  LONGBLOB	TINYTEXT
  LONGTEXT	VARCHAR (n)
  MEDIUMBLOB

• Numeric data:

  BIGINT (n)	INT (n)
  DECIMAL (length, decimals)	MEDIUMINT (n)
  DOUBLE (length, decimals)	SMALLINT (n)
  FLOAT (length, decimals)	TINYINT (n)

  Note: When performing calculations on numeric values and when storing numeric values, SAS maintains up to 15 digits of precision. When you read values that contain more than 15 decimal digits of precision from a database into SAS, the values that SAS reads are rounded to meet this condition. When you use a large numeric value in a WHERE clause, this rounding can cause unexpected results, such as not selecting desired rows. For noncomputational purposes, such as storing ID values or credit card numbers, you can read the data in as character data. For more information, see Your Options When Choosing Your Needed Degree of Precision.
• Date, time, and timestamp data:

  DATE	TIME
  DATETIME	TIMESTAMP

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.

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.