MySQL Tutorial/Data Types/Introduction

Data type mapping occurs at table creation time, after which the original type specifications are discarded.

   <source lang="sql">

mysql> mysql> CREATE TABLE t (a BOOL, b FLOAT8, c LONG VARCHAR, d NUMERIC); Query OK, 0 rows affected (0.01 sec) mysql> mysql> DESCRIBE t; +-------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------+------+-----+---------+-------+ | a | tinyint(1) | YES | | NULL | | | b | double | YES | | NULL | | | c | mediumtext | YES | | NULL | | | d | decimal(10,0) | YES | | NULL | | +-------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> mysql> drop table t; Query OK, 0 rows affected (0.00 sec)</source>

Date Datatypes

There are 5 MySQL date datatypes these are:

Datatype Format Info DATETIME YYYY-MM-DD HH:MM:SS This stores both date and time. DATE YYYY-MM-DD This only stores the date TIMESTAMP(length) Depends on the length field TIME HH:MM:SS This stores only the time YEAR YYYY Stores only the year

Numeric Types

Type Name Value Range Unsigned TINYINT -128 to 127 0-255 SMALLINT -32768 to 32767 0-65535 MEDIUMINT -8388608 to 8388607 0-16777215 INT -2147483648 to 2147483647 0-4294967295 BIGINT -9223372036854775808 - 9223372036854775807 0-18446744073709551615 FLOAT(M,D) Varies depending on values Varies depending on values DOUBLE(M,D) Varies depending on values Varies depending on values DECIMAL(M,D) Varies depending on values Varies depending on values

If the column is numeric and declared UNSIGNED, the range doubles for the given type.

FLOATs, DOUBLEs, and DECIMALs can hold fractions.

The other types cannot.

The first number is the number of digits for the whole number and the second is the number of digits for the fraction.

You can limit the number of digits to the right of the decimal point.

For 5.6876, FLOAT(4,2) type column would store 5.69.

FLOAT(4,3) would be store 5.688.

MySQL rounds the decimal to fit the number of digits right of the decimal point.

Numeric Types Storage

Type Name Memory Space TINYINT 1 byte SMALLINT 2 bytes MEDIUMINT 3 bytes INT 4 bytes BIGINT 8 bytes FLOAT(M,D) 4 bytes DOUBLE(M,D) 8 bytes DECIMAL(M,D) The value of M + 2 bytes

Storage Requirements for Date and Time Types

Data Type Storage Required DATE 3 bytes DATETIME 8 bytes TIMESTAMP 4 bytes TIME 3 bytes YEAR 1 byte

Storage Requirements for Numeric Types

Data Type Storage Required TINYINT 1 byte SMALLINT 2 bytes MEDIUMINT 3 bytes INT, INTEGER 4 bytes BIGINT 8 bytes FLOAT(p) 4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53 FLOAT 4 bytes DOUBLE [PRECISION], REAL 8 bytes DECIMAL(M,D), NUMERIC(M,D) Varies; see following discussion BIT(M) approximately (M+7)/8 bytes

The basic datatypes

Datatype Description Example: INT Numeric entry id INT VARCHAR(n) Text string of characters up to n with a maximim of 255 characters myVarChar VARCHAR(20) CHAR(n) Text string with specific number (n) of characters. myChar CHAR(30) TEXT Holds between 255 - 65535 characters myText TEXT DATE The date stored in the format YYYY-MM-DD myDate DATE TIME The time stored in the format HH:MM:SS myTime TIME

The syntax for defining a field and datatype is "fieldname datatype".

If string length in CHAR(n) Column is less than "n" then the string is padded by spaces (spaces are removed when data is retrieved).

n for CHAR(n) is 255 Maximum.

The format of the zero value for each type.

Note that the use of these values produces warnings if the NO_ZERO_DATE SQL mode is enabled.

Data Type Zero Value DATETIME "0000-00-00 00:00:00" DATE "0000-00-00" TIMESTAMP "0000-00-00 00:00:00" TIME "00:00:00" YEAR 0000

Using Data Types from Other Database Engines

MySQL maps data types as shown in the following table.

Other Vendor Type MySQL Type BOOL TINYINT BOOLEAN TINYINT CHARACTER VARYING(M) VARCHAR(M) FIXED DECIMAL FLOAT4 FLOAT FLOAT8 DOUBLE INT1 TINYINT INT2 SMALLINT INT3 MEDIUMINT INT4 INT INT8 BIGINT LONG VARBINARY MEDIUMBLOB LONG VARCHAR MEDIUMTEXT LONG MEDIUMTEXT MIDDLEINT MEDIUMINT NUMERIC DECIMAL

ZEROFILL,UNSIGNED,SERIAL,SERIAL DEFAULT VALUE

A "ZEROFILL" numeric column will add the UNSIGNED attribute to the column.

UNSIGNED numeric data types also allow SIGNED.

UNSIGNED numeric data types are signed by default.

SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

SERIAL DEFAULT VALUE in the definition of an integer column is an alias for NOT NULL AUTO_INCREMENT UNIQUE.

The ZEROFILL can display leading zeros of a number based on the display width.

For example, if you declare an INT(8) ZEROFILL, and the value you"re storing is 23, it will be displayed as 00000023.

10. 16. Introduction 10. 16. 1. <A href="/Tutorial/MySQL/0200__Data-Types/Thebasicdatatypes.htm">The basic datatypes</a> 10. 16. 2. <A href="/Tutorial/MySQL/0200__Data-Types/DateDatatypes.htm">Date Datatypes</a> 10. 16. 3. <A href="/Tutorial/MySQL/0200__Data-Types/NumericTypesStorage.htm">Numeric Types Storage</a> 10. 16. 4. <A href="/Tutorial/MySQL/0200__Data-Types/NumericTypes.htm">Numeric Types</a> 10. 16. 5. ZEROFILL,UNSIGNED,SERIAL,SERIAL DEFAULT VALUE 10. 16. 6. <A href="/Tutorial/MySQL/0200__Data-Types/UsingDataTypesfromOtherDatabaseEngines.htm">Using Data Types from Other Database Engines</a> 10. 16. 7. <A href="/Tutorial/MySQL/0200__Data-Types/Datatypemappingoccursattablecreationtimeafterwhichtheoriginaltypespecificationsarediscarded.htm">Data type mapping occurs at table creation time, after which the original type specifications are discarded.</a> 10. 16. 8. <A href="/Tutorial/MySQL/0200__Data-Types/StorageRequirementsforNumericTypes.htm">Storage Requirements for Numeric Types</a> 10. 16. 9. <A href="/Tutorial/MySQL/0200__Data-Types/StorageRequirementsforDateandTimeTypes.htm">Storage Requirements for Date and Time Types</a> 10. 16. 10. <A href="/Tutorial/MySQL/0200__Data-Types/Theformatofthezerovalueforeachtype.htm">The format of the zero value for each type.</a>