6.2.5 Using Column Types from Other Database Engines

To make it easier to use code written for SQL implementations from other vendors, MySQL maps column types as shown in the following table. These mappings make it easier to move table definitions from other database engines to MySQL:

Column type mapping occurs at table creation time. If you create a table with types used by other vendors and then issue a DESCRIBE tbl_name statement, MySQL reports the table structure using the equivalent MySQL types.

User Comments

Regarding a tutorial, you may find this useful :
http://devshed.com/Server_Side/MySQL/Grant_Tables/

More info on using multiple columns in indexes
would be very useful here. Especially what
happens when using multiple columns with a UNIQUE
index!

To change an enum column (if you want to add a
value) the syntax is as follows:

ALTER tableName MODIFY columnName enum
("value1","value2","value3"...);

Remember to include your old values! Is there a
way of adding a new value on its own?

The manual entry here does not explain how to
update a set without keeping the existing
entries. A proper definition is needed.

ALTER TABLE where you change an ENUM definition
appears to correctly handle value changes as well
as values inserted, without adverse affect on
existing data.

However, I have not tested combinatorial changes,
such as inserting a new value plus changing the
label of a later value. I wouldn't expect the
database to be very smart about that.

I have noticed an irregularity with DECIMAL data types:


<DL>
<DD>DECIMAL(1,0) is 2 bytes, not 1 (the negative sign uses one byte, in the positive this extra byte is used by another digit)


<DD>DECIMAL(5,4) is 8 bytes, not 7 (MySQL assumes that there are always 2 digits before a decimal)


<DD>DECIMAL(5,7) is 11 bytes, although technically this should not be allowed (MySQL automatically turns the 5 into a 7, but as always, assumes there are 2 digits before a decimal, so instead of this being .0000000, it is actually 00.0000000)
</DL>
Hope this helps clear some confusion reguarding this data type...

Other column types that are supported in MS SQL
Server that must be changed to work for MySQL
include:
money->real (or double)
varchar[n]->text (or blob) where n > 255

Looking for a BOOLEAN data type in MySQL™?

NOTE: This comment is an amendment to my original proposal:

As a professional Programmer/Analyst by trade, I have much experience with application development and databases. Mainly Microsoft® SQL Server™ and MySQL™. In SQL Server™, a boolean data type is called a BIT, meaning 0 or 1, true or false. I think the MySQL™ developers need to add a BOOLEAN data type that ONLY stores either a 0 or 1. But until then I think the best method is to use a TINYINT(1) UNSIGNED NOT NULL.

Although the MySQL™ documentation states that a TINYINT(1) is a synonym for BIT and BOOL data types, it is not the most logical method. Simply because 0 equals false and a value between 1 and 127 equals true (or 1 to 255 equals true in the unsigned version). Although this method is efficient in only requiring 1 byte of storage, it is an illogical solution because of the possibility of other values >1 being allowed.

Another alternative is the CHAR(0) datatype. The ideology here is that a value of NULL equals false and a value of "" (empty string, non-null value) equals true. Again, an excellent form of efficiency in only storing 1 byte of data. The drawback however is that some scripting engines and languages cannot differentiate between a NULL and a "" (empty string), thus making your different data, essentially, the same or equal to each other.

My original proposal was to mimic a boolean datatype in MySQL™ using an ENUM('0','1') NOT NULL. If an ENUM is not declared as NOT NULL, then a NULL value will be allowed and the default value is NULL. I would also suggest defaulting it to a value of '0' or '1', depending on the most common value for the situation. Yes, the ENUM stores a string so 0 is really '0' and 1 is really '1'. As far as storage is concerned I'm not 100% positive whether it is 1 or 2 bytes, although rest assured it is a maximum of 2 bytes. This discrepancy exists because of lack of clarity in the MySQL™ documentation. It states that the storage required for a column of type ENUM, is 1 or 2 bytes, depending on the number of enumeration values. But since 2 enumeration values is minute compared to it's maximum of 65535 values, perhaps it is only storing 1 byte of data. Especially since the data being stored is really only 1 byte in length, a zero or one.

You may ask, why not use ENUM('y','n') or ENUM('true','false')? The storage size will be identical for the ENUM('y','n') but for backward and forward-compatibility most enterprise databases use 0 or 1 for booleans. So in the event that you would want to switch or replicate to another database provided by a different vendor, your data would most likely need not be updated before exporting and importing. Additionally, most programming languages use 0's and 1's, or constants that evaluate to 0's and 1's, as values when working programmatically, especially with checkboxes in a visual environment, thus requiring less cast conversions before executing an SQL command. However, if the front-end your working with uses 'true' or 'false' values explicitly, 'yes' or 'no' values, or 'on' or 'off' values, then set your ENUM to the respective common values. But keep in mind that if you use ENUM('true','false') then you will most likely be storing 2 bytes as opposed to 1 byte for each record. A true boolean stores 1 byte of data consisting of either integers: 0 or 1. Ideally, a boolean should store 1 bit. (8 bits = 1 byte). But some booleans have the potential for a null or uninitialized value.

However, after some careful consideration and further development in MySQL™, I have decided against the ENUM data type. The reasoning is that ENUM's are stored as strings; thus requiring a cast conversion. You should not have to quote your boolean value to perform SQL commands. I still hate the fact that a TINYINT(1) UNSIGNED allows for a number between 0 and 255. However, I beleive this is better than anything else I've seen; for MySQL™ at least. As long as one follows the rationale that 0 is false and a value greater than or equal to 1 and not greater than 255 is true. I also suggest the NOT NULL constraint because a boolean should be on or off! Not unitialized! A default value of 1 or 0 should be given at the DDL level, and can actually make your programming a lot easier by using default values if one is not specified. In scenarios where one is inserting a new record but yet the record can later be disabled, it is good to use 1 as a default value (example: adding new categories or defined types of some sort). It is good to use 0 as a default value in scenarios where a forceful update must occur on the record (example: having a user activate an account that he/she recently registered). Another benefit for using this data type is that it only requires 1 byte of storage! According to the MySQL™ documentation, a column of type TINYINT requires 1 byte of storage, thus making it the most efficient storage method thus far. The only thing possibly for efficient would be a bit.

Thus, I conclude, that until MySQL™ develops a true boolean datatype, using a TINYINT(1) UNSIGNED NOT NULL, is the best solution to date.

References:
http://www.mysql.com/doc/en/Column_types.html
http://www.mysql.com/doc/en/ENUM.html
http://www.mysql.com/doc/en/Storage_requirements.html
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_2it0.asp

More on boolean data type:

TINYINT (1) UNSIGNED works with VB.NET when attempting to share the same code with an OLE DB to an Access database and an ODBC connection to MySQL. The Enum property does not work in this situation. You would need separate Dataset definitions for each connection type if you used enum.

Regarding Corey's discussion of use of boolean values, I suspect that MySQL includes no such data type because there is no such data type in formal RDMS theory. This follows the rational that a boolean value denotes a qualitative state that can be normalized as some more general nominal value. For instance, whether or not someone is blonde can be more aptly defined by a statement of hair color, thus eliminating a related series of values (blonde [y/n], brown [y/n], red [y/n], etc.) by combining them into a single qualitative domain (hair color [blonde, brown, red, ...]).

As regards the above comments on boolean/enum.

Its true that Codd didn't talk about booleans, but that does not mean they have no place in relational algebra... It just means that like SSNs or such they are not a FUNDAMENTAL type, they are a composed type. So MySQL composes them from single byte integers. Not surprising.

The objection to the TINYINT-as-BOOL may be true, but its pretty nit-picky. I know of no modern data processing system that would choke on 12 (for example) representing true. The bad part would be 12 and 13 could be 'true but not equal' values. So its possible we could use a true boolean type, though it would of course still take up a byte.

The enum solution really does not work, for example enum values have to be quoted, so you would have to have SQL like INSERT INTO (boolcol) mytable VALUES('1') which isn't really the same as INSERT INTO (boolcol) mytable VALUES(1) and ODBC is completely bent about ENUM anyway...

Add your own comment.