CoderZone.org
Pages: 1 « previous     next »
  Print  
Author Topic: MySQL and enum  (Read 18379 times) Bookmark and Share
phpMan2010
Newbie
*
Posts: 32



View Profile
« on: Jan 24, 2011, 10:06:55 am »

I keep looking at enum columns and wondering why I'm not using them more.

Ideas?  Best practices?
Logged
Bill220
Newbie
*
Posts: 4



View Profile
« Reply #1 on: Jan 25, 2011, 09:37:03 am »

I never enum...I think tha for a lot of the stuff that I do an enum column doesn't make sense. I guess I also don't see the advantage in using them. (??)
Logged
phpMan2010
Newbie
*
Posts: 32



View Profile
« Reply #2 on: Jan 26, 2011, 06:07:32 am »

I saw it in one of those best practices lists - enums are stored as integers, although they're accessed as strings.  It can save you from having to write annoying constant translation lists, where you use and integer to represent something, like a status.

From the create table / alter table add column:

`status` enum('pending','activated','locked','disabled') default 'pending',

And the associated .ini settings for Zend Framework, using dojo's FilteringSelect to allow and administrator to assign the status:

; status element
; These MUST match the definitions in the user table
elements.status.type = "FilteringSelect"
elements.status.options.label = "Status"
elements.status.options.dijitParams.searchAttr = "name"
elements.status.options.autoComplete = true
elements.status.options.required = "true"
elements.status.options.validators.inarray.validator = "InArray"
elements.status.options.validators.inarray.options.haystack[] = "pending"
elements.status.options.validators.inarray.options.haystack[] = "activated"
elements.status.options.validators.inarray.options.haystack[] = "disabled"
elements.status.options.validators.inarray.options.haystack[] = "locked"
elements.status.options.validators.inarray.options.messages.notInArray = "Invalid status"
elements.status.options.multioptions.pending = "pending"
elements.status.options.multioptions.activated = "activated"
elements.status.options.multioptions.disabled = "disabled"
elements.status.options.multioptions.locked = "locked"

Finally, it's multilingual, xgettext will translate any text it can find a definition for into another layer.

I was talking with the database guy at work, and he said enums aren't portable across databases.

A quick look on the 'net showed that Oracle didn't seem to have direct enum support.

It may also be a security boost - since invalid enum values are stored as '' (empty strings).
Logged
cuberat
Newbie
*
Posts: 40


View Profile
« Reply #3 on: Jan 26, 2011, 02:37:17 pm »

that looks a lot more complicated than just using string constants

what is all that stuff?
Logged
phpMan2010
Newbie
*
Posts: 32



View Profile
« Reply #4 on: Jan 27, 2011, 05:41:32 am »

The .ini settings aren't really related to the question, they were the only text I had to support the enum question.

A lot of times, I need an array to assign strings to values for databases - like this:

Code:  
Highlight Mode: (PHP)
  1. $aStatusMap = array(0=>'pending',1=>'activated',2=>'locked',3=>'disabled');
 

And then when the (integer) data comes out of the database, I translate it:

Code:  
Highlight Mode: (PHP)
  1. $sStatus = $aStatusMap[$database_value['status']];
 

But if I used an enum type, it would be:
Code:  
Highlight Mode: (PHP)
  1. $sStatus = $database_value['status'];
 

If the database is accessed by different (programming) languages or code, the string values will be preserved and accurate, because they are in the database, rather than defined in code.  In addition, if the database changes, the code has to change.

PHP to get the enum values and default from the database:
Code:  
Highlight Mode: (PHP)
  1. if ($result = $mysqli->query("SHOW COLUMNS FROM test LIKE 'enum_col'")) {
  2.        $row = $result->fetch_assoc();
  3.        $sType = $row['Type'];
  4.        $aValues = explode(',',str_replace('\'','',substr($sType,(strpos($sType,'(')+1),-1)));
  5.        echo 'Field name: '.$row['Field'].PHP_EOL
  6.                .'Valid values: '.var_export($aValues,true).PHP_EOL
  7.                .'Default: '.$row['Default'];
  8.        $result->close();
 

Output:

Quote
Field name: enum_col
Valid values: array (
  0 => 'one',
  1 => 'two',
  2 => 'three',
)
Default: two

And Perl code:
Code:  
Highlight Mode: (Perl)
  1. #!/usr/bin/perl
  2. use DBI;
  3. $dbh = DBI->connect('DBI:mysql:test', '', ''
  4.                   ) || die "Could not connect to database: $DBI::errstr";
  5.  
  6. $sth = $dbh->prepare('SHOW COLUMNS FROM test LIKE \'enum_col\'');
  7. $sth->execute();
  8. $result = $sth->fetchrow_hashref();
  9. print "Values returned: $result->{Type}\n";
  10. print "Default: $result->{Default}\n";
  11. $sth->finish();
  12. $dbh->disconnect();
  13.  
 

Output:

Quote
Values returned: enum('one','two','three')
Default: two

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

Is this a good idea, or am I missing something?



  

Logged
UnrealEd
Newbie
*
Posts: 22



View Profile
« Reply #5 on: Jan 27, 2011, 06:51:40 am »

I hardly use enum fields, because it's too limited towards user-preferences.

Using your status example: suppose and admin would like to add a new status, he would have to edit the database. Something that shouldn't be done when in use (unless it is absolutely necessary), cause there's always a risk it won't work afterwards.

Instead of enums I use "definition" tables (the term is something I made up, but there might be an official term). These are tables with a simple id and a value field. For instance, your statusses would be stored in a table called "status", each status represented as a new entry. That way the code is modular as any admin can add a new status, or remove one. Using a simple FOREIGN KEY one can set a sort of fixed relation between a table and the status table.
Logged
phpMan2010
Newbie
*
Posts: 32



View Profile
« Reply #6 on: Jan 27, 2011, 07:05:37 am »

I like that.  It keeps the values in the database, so different languages can still use them, and it uses integers for the data which is good for performance, it insulates the value from its meaning, and it's more flexible and portable than enums.

Thank you.
Logged
Flynn
Newbie
*
Posts: 9



View Profile
« Reply #7 on: Jan 27, 2011, 09:51:25 pm »

I agree with both of you, lol.

phpman has a point, it's a nice way to abstract all the values so they can be used or referenced without regard to the linked value. The mapping trick is a nice idea, I like that too. It would be good for localization and language handling.

But, I also agree with UnrealEd...it seems kind of restrictive in terms of flexibility if stuff has to be added or changed, and it seems to me that the changes might end up affecting stuff way downstream where you might not catch it for a while. In that respect it would make me nervous if I had a lot of stuff built referencing the enum values and depending on them to be constant.

I don't use enum myself (I don't think I've ever used it, actually). It's described as a way to make sure that only specific values from a set of choices are able to be entered into the database, but I do that in the php layer and I just feel more comfortable doing that way...maybe because I can see the values in the code directly.

I don't know....I'm sure it's there for a reason and I'm sure it makes sense in the right application, I guess I've never had a situation where I thought it would be the perferred way to do it. I'm also not a sql guru, so that might be why too, lol.
Logged
Tags:
Pages: 1
  Print  
 
Jump to: