SQLDB data type

Data types and ranges used by Microsoft Access, MySQL, and SQL Server.

Microsoft Access Data Type

Data Type	Description	Storage
Text	is used for text or a combination of text and numbers. Maximum 255 characters.
Memo	Memo is used for larger amounts of text. Stores up to 65,536 characters. Note: The memo field cannot be sorted. But they are searchable.
Byte	Allows numbers from 0 to 255.	1 Byte
Integer	All numbers between -32,768 and 32,767 are allowed.	2 Bytes
Long	All numbers between -2,147,483,648 and 2,147,483,647 are allowed.	4 Bytes
Single	Single precision floating point. Handles most decimals.	4 Bytes
Double	Double precision floating point. Handles most decimals.	8 Bytes
Currency	is used for currency. Supports 15 digits, plus 4 decimal digits. Tips: You can choose which country’s currency to use.	8 Bytes
AutoNumber	The AutoNumber field automatically assigns a number to each record, usually starting with 1.	4 Bytes
Date/Time	For date and time	8 Bytes
Yes/No	Logical field, which can be displayed as Yes/No, True/False or On/Off. In your code, use the constants True and False (equivalent to 1 and 0). Note: Null values are not allowed in Yes/No fields	1 bit
Ole Object	can store pictures and audio , videos or other BLOBs (Binary Large OBjects).	Up to 1GB
Hyperlink	Contains links to other files, including web pages.
Lookup Wizard	Allows you to create a list of options that can be selected from a drop-down list.	4 Bytes

MySQL data types

In MySQL, there are three main types: Text (text), Number (number) and Date/Time (date/time) type.

Text Type:

Data Type	Description
CHAR(size)	Save a fixed-length string (can contain letters, numbers, and special characters). Specify the length of the string in parentheses. Maximum 255 characters.
VARCHAR(size)	Save a variable-length string (can contain letters, numbers, and special characters). Specify the maximum length of the string in parentheses. Maximum 255 characters. Note: If the length of the value is greater than 255, it is converted to the TEXT type.
TINYTEXT	Stores a string with a maximum length of 255 characters.
TEXT	Stores a string with a maximum length of 65,535 characters.
BLOB	is used for BLOBs (Binary Large OBjects). Stores up to 65,535 bytes of data.
MEDIUMTEXT	Stores a string with a maximum length of 16,777,215 characters.
MEDIUMBLOB	is used for BLOBs (Binary Large OBjects). Stores up to 16,777,215 bytes of data.
LONGTEXT	Stores a string with a maximum length of 4,294,967,295 characters.
LONGBLOB	is used for BLOBs (Binary Large OBjects). Stores up to 4,294,967,295 bytes of data.
ENUM(x,y,z,etc.)	Allows you to enter a list of possible values. A maximum of 65535 values can be listed in the ENUM list. If the inserted value does not exist in the list, a null value is inserted. Note: The values are sorted in the order you enter them. Possible values can be entered in this format: ENUM('X','Y','Z')
SET	Similar to ENUM, the difference is that SET can only contain up to 64 list items and SET can store more than one selection.

Number Type:

Data type	Description
TINYINT( size)	-128 to 127 regular. 0 to 255 unsigned*. Specify the maximum number of digits in parentheses.
SMALLINT(size)	-32768 to 32767 General. 0 to 65535 unsigned*. Specify the maximum number of digits in parentheses.
MEDIUMINT(size)	-8388608 to 8388607 Normal. 0 to 16777215 Unsigned*. Specify the maximum number of digits in parentheses.
INT(size)	-2147483648 to 2147483647 General. 0 to 4294967295 Unsigned*. Specify the maximum number of digits in parentheses.
BIGINT(size)	-9223372036854775808 to 9223372036854775807 General. 0 to 18446744073709551615 Unsigned*. Specify the maximum number of digits in parentheses.
FLOAT(size,d)	Decimal number with floating decimal point. Specify the maximum number of digits in the size parameter. Specify the maximum number of digits to the right of the decimal point in the d parameter.
DOUBLE(size,d)	A large number with a floating decimal point. Specify the maximum number of digits in the size parameter. Specify the maximum number of digits to the right of the decimal point in the d parameter.
DECIMAL(size,d)	DOUBLE type stored as a string, allowing fixed decimal points. Specify the maximum number of digits in the size parameter. Specify the maximum number of digits to the right of the decimal point in the d parameter.

*These integer types have the additional option UNSIGNED. In general, integers can be negative or positive. If you add the UNSIGNED attribute, the range will start at 0 instead of some negative number.

Date Type:

##DATETIME()*A combination of date and time. Format: YYYY-MM-DD HH:MM:SSTIMESTAMP()*Timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD HH:MM:SSTIME() time. Format: HH:MM:SSYEAR()Year in 2-digit or 4-digit format.

*Even though DATETIME and TIMESTAMP return the same format, they work very differently. In an INSERT or UPDATE query, TIMESTAMP automatically sets itself to the current date and time. TIMESTAMP also accepts different formats, such as YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD.

SQL Server Data Type

String Type:

Data Type	Description
DATE()	Date. Format: YYYY-MM-DD Notes: The supported range is from '1000-01-01' to '9999-12-31'
	Note: The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59 :59'
	Note: The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03: 14:07' UTC
	Notes: The supported range is from '-838:59:59' to '838:59:59'
	Comments: Values allowed in 4-digit format: 1901 to 2155. Values allowed in 2-digit format: 70 to 69, representing from 1970 to 2069.

##Data TypeDescriptionStoragechar(n)Fixed length string. Maximum 8,000 characters. Defined widthvarchar(n)Variable length string. Maximum 8,000 characters. 2 bytes + number of charsvarchar(max)Variable length string. Maximum 1,073,741,824 characters. 2 bytes + number of charstextVariable length string. Up to 2GB of text data. 4 bytes + number of charsnchar Fixed length Unicode string. Maximum 4,000 characters. Defined width x 2nvarcharVariable length Unicode string. Maximum 4,000 characters. nvarchar(max)Variable length Unicode string. Maximum 536,870,912 characters. ntextA variable-length Unicode string. Up to 2GB of text data. ##bit##binary(n)Fixed-length binary string. Maximum 8,000 bytes. varbinaryA variable-length binary string. Maximum 8,000 bytes. varbinary(max)A variable-length binary string. Up to 2GB. imageA variable-length binary string. Up to 2GB.

Number Type:










Allows 0, 1 or NULL

Data type	Description	Storage
tinyint	All numbers from 0 to 255 are allowed.	1 byte
smallint	All numbers between -32,768 and 32,767 are allowed.	2 Bytes
int	All numbers between -2,147,483,648 and 2,147,483,647 are allowed.	4 bytes
bigint	All numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 are allowed.	8 bytes
decimal(p,s)	Number with fixed precision and scale. Allows numbers from -10^38 +1 to 10^38 -1. The p parameter indicates the maximum number of digits (left and right of the decimal point) that can be stored. p must be a value between 1 and 38. The default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value between 0 and p. The default is 0.	5-17 bytes
numeric(p,s)	Number with fixed precision and scale. Numbers from -10^38 +1 to 10^38 -1 are allowed. The p parameter indicates the maximum number of digits (left and right of the decimal point) that can be stored. p must be a value between 1 and 38. The default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value between 0 and p. The default is 0.	5-17 bytes
smallmoney	Money data between -214,748.3648 and 214,748.3647.	4 bytes
money	Money data between -922,337,203,685,477.5808 and 922,337,203,685,477.5807.	8 bytes
float(n)	Floating precision numeric data from -1.79E + 308 to 1.79E + 308. The n parameter indicates whether the field holds 4 bytes or 8 bytes. float(24) holds 4 bytes, while float(53) holds 8 bytes. The default value of n is 53.	4 or 8 bytes
real	Floating precision numeric data from -3.40E + 38 to 3.40E + 38.	4 Bytes

Date type:

Data type	Description	Storage
datetime	From January 1, 1753 to December 31, 9999, with an accuracy of 3.33 milliseconds.	8 bytes
datetime2	From January 1, 1753 to December 31, 9999, with 100 nanosecond precision.	6-8 bytes
smalldatetime	From January 1, 1900 to June 6, 2079, with an accuracy of 1 minute.	4 Bytes
#date	Stores only the date. From January 1, 0001 to December 31, 9999.	3 bytes
time	Only stores time. The accuracy is 100 nanoseconds.	3-5 bytes
datetimeoffset	Same as datetime2, plus time zone offset.	8-10 Bytes
timestamp	Stores a unique number that is updated whenever a row is created or modified. The timestamp value is based on the internal clock and does not correspond to real time. There can be only one timestamp variable per table.

Other data types:

Data type	Description
sql_variant	Stores up to 8,000 bytes of data of different data types, except text, ntext, and timestamp.
uniqueidentifier	Stores a globally unique identifier (GUID).
xml	Storage XML formatted data. Up to 2GB.
cursor	Stores a reference to a pointer used for database operations.
table	Stores the result set for later processing.

SQLDB data type

Microsoft Access Data Type

MySQL data types

SQL Server Data Type

php.cn