1

I am curious about MySQL ENUM datatype. For example if I have a few possible strings for the field is it better to use a string, or just a number and then to decode the number into a string using for example PHP?

For example:

ENUM("Dog", "Cat", "Fish");

VS

ENUM(1,2,3);

and then use something like:

<?php
$values = Array("Dog", "Cat", "Fish);
$result = mysql_query('SELECT `animal` FROM `example_table`');
$item = mysql_fetch_object($result);
$animal = $item->type;
?>

Or is it the best case to use tinyint in such a case where possible entries are fewer than 128(1 byte) ?

I would like to know the advantages/disadvantages in case of performance and space.

Thank you

lukas.pukenis
  • 13,057
  • 12
  • 47
  • 81

2 Answers2

6

MySQL uses integers internally for ENUM fields, so it wouldn't make that much of a difference.

The results of this test show how fast ENUM really is in comparison to VARCHAR or INT with a JOIN to get the string values.

Community
  • 1
  • 1
Matthew
  • 15,464
  • 2
  • 37
  • 31
4

It is fine to use:

ENUM("Dog", "Cat", "Fish");

Internally MySQL stores enum values as integer. So in this case there is no need to perform extra optimization inside PHP.

udachny
  • 415
  • 7
  • 13