Convert myql enum to number

2008/03/16 by Lassi A. Liikkanen

Convert MySQL enum fields to keyed number fields

WORK IN PROGRESS

This guide instructs you how to migrate from enum type of fields to keyd references, i.e. so that your table of interest holds only the index number (as with enum) but the label for the index is stored in a different label.

Background

At some point in my LAMP hobbies, I discovered that using ENUM for storing indexed alternatives, like category labels, was a great idea. It provided access to both labels and index numbers. I still think it's good, but I've also stumbled upon its limitations. The biggest problem occurs when you would like reference the enum fields from another table or combined query. The problem lies with the fact that most operations are carried out with the string values and not index numbers. So, I eventually had to convert some fields from enum to tinyint and compile the labels to a different table. An additional difficulty I encountered was the fact that during the years, MySQL is posing more strict requirements for ISAM-type enum fields and you can't have duplicated values in enum fields anymore without encountering an error (Column 'a' has duplicated value 'x' in ENUM). A side-effect is that if you previously had empty looking NOT NULL fields they may indeed have an (arbitrary) index value.

How to do it

In this example, I have two tables. documents contains originally an ENUM field which I will convert to use with a keyed table, categories First create a table that will be functionally similar to your previous ENUM field. E.g.

CREATE TABLE 'categories' (
  'id' tinyint(3) unsigned NOT NULL,
  'label' char(32) default NULL,
  PRIMARY KEY  ('id'),
  KEY 'id' ('id')
) ;

If you'd like to use SQL query to import label data then you should select AUTO_INCREMENT option for id as well.

The next step depends on your MySQL version and table type. The easiest option is to load the ENUM values into the new table and then insert the labels. Then you must change the original field. At easist, this can be done in the following style:

ALTER TABLE `documents`
CHANGE `grouping` `grouping` TINYINT(3) UNSIGNED NULL;

But this may not work, so you need to create temporary fields or even a table to hold the fields to make the transition. The latter option might be required if you're previous ENUM-based solution has duplicated values and prevents you totally from adding new fields. One option is to export (dump) the whole database and import it with a new name (you'll have to replace all name references) and change the primary key definition if necessary.

Create the temporary table and populate it with the data of the original table (documents here) to a temporary table (temptable )in the right format using following kind of syntax:

# create
CREATE TABLE temptable
(`id` MEDIUMINT UNSIGNED,
`category` TINYINT UNSIGNED,
`grouping` TINYINT UNSIGNED,
)
TYPE = InnoDB;

#populate
INSERT INTO temptable (id, category)
SELECT id, category+0
FROM documents;

And then you will need a rule to NULL the entried which where intentionally left null but are now converted into numeric references to an originally empty ENUM field. In this case, I have manually checked that number 128 corresponds to a NULL entry and so I do the following:

UPDATE temptable
SET category= NULL
WHERE category=128;

Now we are quite ready to import the modified data back to the original table. This should start by dropping the original ENUM fields.

ALTER TABLE `documents` DROP `category`

However, this may fail (column 'category' has duplicated value '' in ENUM)as well if the problem describe above exists. In this case, there is no other chance than to duplicate the the original table BUT without the problematic ENUM type fields. I would suggest using CHAR /VARCHAR fields instead and observe any possible problems with KEY definitions.

But once you're rid of the old fields, then everything goes smoothly, do an update query and you'll have the data as you wanted in the first.

UPDATE documents, temptable
SET documents.category=temptable.category
WHERE documents.id=temptable.id;

Now, the only thing remaining is to modify all queries that rely on getting ENUM string info. But this is another (much more simpler) issue.

References

http://dev.mysql.com/doc/refman/5.0/en/enum.html
http://dev.mysql.com/doc/refman/5.0/en/update.html

Related content:
An example how to convert URL addresses to HTML links, 2009/01/26

No comments for this page
Add comment | Show all comments

Name*:
Email*:
Your email will not be shown publicly or disclosed to third parties, it is used to validate messages
Location (City, Country):
* This information must be provided

Keywords: [php] , [computers] Document's status: Ok (Document dates explained)

This document created: 2008/03/16
Modified: 2008/03/16
Published: 2008/03/16


This document's permanent URI (linking):
http://iki.fi/lassial/guides/php/080316-Convert_myql_enum_to_number

© Lassi A. Liikkanen 2008 - 2016. All rights reserved.
^Top of the Page^

*Change layout:
Printable printable
Large text

Myydään:

@lassial Twitter feed: