Tipo ENUM no MySQL e no PostgreSQL

Tempos atrás eu publiquei aqui, sobre os tipos ENUM e SET no MySQL, porém as respostas dos leitores deixaram claro que eu não havia explicado o assunto direito. E relendo o texto, realmente assumo que ficou péssimo. Não consegui passar o conceito, quanto menos a aplicabilidade desses recursos.

Então aqui cabe a redenção. Vamos ao tipo ENUM. Ele não é um tipo definido no padrão SQL, e sim implementações proprietárias, encontrado nos bancos de dados mais comuns. Assim seu comportamento, espaço em disco e queries que podem ser usadas diferem ligeiramente entre suas implementações, mas a idéia por trás do tipo ENUM é comum a todos.

As vantagens do uso do tipo ENUM são:

• ganho de performance
• segurança dos dados

As desvantagens são:

• limites de uso (a alteração requer a reestruturação da tabela)
• a não portabilidade devido as diferenças de implementação

A principal característica do tipo ENUM é permitir a criação de uma lista enumerada, sendo cada registro um par index-value. A idéia é bem simples mesmo, mas extremamente eficaz e poderosa. O uso do tipo ENUM deve ser feito em parâmetros onde há pouca ou nenhuma alteração durante a existência da aplicação - por exemplo, sexo, ou um sistema de rating com valores fixos de entrada.

CREATE TABLE 'myPics' (
'id' INT NOT NULL AUTO_INCREMENT ,
'url' VARCHAR( 255 ) NOT NULL ,
'rate' ENUM('bad', 'good', 'excellent') NULL DEFAULT 'good',
PRIMARY KEY ('id') ,
INDEX ('url')
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_bin;

Na tabela de exemplo acima, criamos o campo rate, que deve armazenar a classificação da foto. Os possíveis valores são: NULL, 'bad', 'good' e 'excellent'. Ou seja você tem valores pré-definidos para o input de dados, e a cada um é atribuído um índice na ordem em que foram declarados. Os possíveis valores para o nosso campo são:

valor	índice
NULL	NULL
''	0
bad	1
good	2
excellent	3

Na hora da query uma possível construção seria:

SELECT name, rate FROM myPics WHERE rate >= 'good';

Isso demonstra a utilidade do tipo ENUM. Ao invés de criar uma tabela auxiliar e atribuir valores a ela, podemos reduzir essa complexidade fazendo uso do tipo ENUM em nossa arquitetura. Ah sim, qualquer função que demande tipos numéricos (SUM(), AVG(), MIN(), MAX(), COUNT()....) podem ser usadas em cima dos campos ENUM. Nesse caso, os valores são primeiro transformados no seu índice numérico.

E vale mencionar mais uma vez que o índice atribuído depende da ordem da declaração dos possíveis valores. Se você declarar ENUM ('bom', 'ruim'), 'bom' terá um valor menor do que 'ruim', e se declarar ENUM ('ruim', 'bom'), 'ruim' terá um valor menor do que 'bom'.

No Postgresql o uso do tipo enum é ligeiramente diferente. O trecho a seguir ilustra o mesmo exemplo anterior, só no que postgre.

CREATE TYPE rating AS ENUM ('bad', 'good', 'excellent');
CREATE TABLE myPics (
id serial,
url varchar(255),
rate rating
);

É necessário criar um tipo de dado com nome próprio do tipo ENUM, e depois atribuí-lo à coluna. Na hora de utilizar, nos beneficiamos dos mesmos recursos de construção.

Agora sobre o tipo SET. O tipo SET só existe no MySQL. E uma possível aplicação para ilustrar sua utilidade seria um sistema de tags, implementado de forma nativa na tabela.

Considere a seguinte tabela:

CREATE TABLE 'myPics' (
'id' INT NOT NULL AUTO_INCREMENT ,
'url' VARCHAR( 255 ) NOT NULL ,
'rate' ENUM('bad', 'good', 'excellent') NULL,
'tags' SET('outdoor', 'studio', 'stock', 'fruits', 'nature', 'close-up', 'people') NULL
PRIMARY KEY ('id') ,
INDEX ('url')
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_bin

Nela, podemos atribuir valores como:

INSERT INTO myPics (id, url, rate, tags) VALUES (NULL, 'xxx', 'good', 'people, close-up'), (NULL, 'yyy', 'excellent', 'fruits, nature, close-up');

Ou seja declarando as tags direto no insert. Na hora de fazer o SELECT, contamos com mais uma função nativa do MySQL bem útil, a FIND_IN_SET:

SELECT * from myPics WHERE FIND_IN_SET('nature', tags) > 0 AND FIND_IN_SET('close-up', tags) > 0;

Isso vai trazer todos os registros cujo campo tags contenha os valores 'nature' e 'close-up'.

Bom, acho que agora o conceito e a aplicação dos tipos ENUM e SET está agora mais clara e mais útil.

-----------
keepReading