CODEWITHSUNDEEP
phpmysql
lukas.pukenis
lukas.pukenis

Reputation: 13597

MySQL enum. Is it better to use strings or numbers

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

Upvotes: 1

Views: 2479

Answers (2)

Matthew
Matthew

Reputation: 16012

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.

Upvotes: 6

udachny
udachny

Reputation: 415

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.

Upvotes: 4

Related Questions