Datatypes: MySQL Compared to Access 2000 and SQL Server 7

only for RuBoard - do not distribute or recompile

Table 1.1 lists data types (column types) for MySQL as they are compared to Access 2000 and SQL Server 7. Note that some types have no corresponding type; those entries are blank.

If you are converting a database or wondering whether your data will fit into one of MySQL s types, Table 1.1 might be of help. Additionally, although two data (or column) types might have the same name , their storage requirements might be different.

Table 1.1. Datatype Conversion Matrix, MySQL, Access2000, and SQL Server 7

Datatype	Storage in Bytes	Access2000	SQL Server 7	Notes
tinyint	1	byte	tinyint
smallint	2	integer	smallint
mediumint	3
int	4	Long Integer	int or integer
bigint	8
float	4	single	float ^[1] or real
double	8	double
decimal	1 per digit + 2		decimal, numeric ^[2]
char	1 per character	text, memo	char
varchar	length + 1		varchar ^[3]
tinyblob	length + 1
blob	length + 2
mediumblob	length + 3
longblob	length + 4		binary, varbinary
tinytext	length + 1			255 max
text	length + 2			65,535 max
mediumtext	length + 3			16,777,215 max
longtext	length + 4
enum	1 for 1 “255 members 2 for 256 “65,535 members
set	1 for 1 “8 members 2 for 9 “16 3 for 17 “24 4 for 25 “32 8 for 33 “64
date	3
time	3
datetime	8	date/time	datetime, timestamp
timestamp	4		smalldatetime	0 is midnight, 1/1/1970
year	1			1900 “2155
	16 for a pointer		image, text
format() ^[4]	8	currency	money
	2 — number of chars		nchar	Fixed length Unicode
	16 + 2 — number of chars		ntext	Variable length Unicode
	2 — number of chars		nvarchar	Unicode
	4		smallmoney
	16		Uniqueidentifier
	12	decimal

^[1] . For float, 4 “8 bytes, depending on the precision specified

^[2] . 5 “17 bytes

^[3] . Length, not length + 1

^[4] .Although it s not a data type, the MySQL FORMAT() function yields the same results.

As you can see, MySQL offers many more choices. MySQL is far more flexible in the integer and text types, but it lacks the dedicated currency types. In essence, MySQL requires you to somewhat know and understand your data ”more than Access or SQL Server do, anyway. MySQL is strongest in the text, numeric, and date types, but it lacks defined money types.

only for RuBoard - do not distribute or recompile