Manual de Referência do MySQL :: 6.5.3 Sintaxe CREATE TABLE

Se nem NULL nem NOT NULL for especificado, a coluna é tratada como se NULL fosse especificado.

Uma coluna integer pode ter o atributo adicional AUTO_INCREMENT. Quando você insere um valor de NULL (recomendado) ou 0 em uma coluna AUTO_INCREMENT indexada, a coluna é definida com o valor da próxima sequência. Normalmente ele é valor+1, onde valor é o maior valor para a coluna column atualmente na tabela. A sequência de AUTO_INCREMENT começa com 1. See Secção 12.1.3.32, “mysql_insert_id()”.

A partir do MySQL 4.1.1, especificando o parâmetro NO_AUTO_VALUE_ON_ZERO para a opção do servidor --sql-mode ou a variável do servidor sql_mode permite que você aramzene 0 nas colunas AUTO_INCREMENT como 0, em vez de gerar uma nova sequência de valores. See Secção 4.1.1, “Opções de Linha de Comando do mysqld”.

Se você deletar a linha contendo o valor máximo para uma coluna AUTO_INCREMENT, o valor será reutilizado por uma tabela ISAM, ou BDB, mas não por tabelas MyISAM ou InnoDB. Se você deletar todas as linhas na sua tabela com DELETE FROM nome_tabela (sem um WHERE) no modo AUTOCOMMIT, a sequencia será reiniciada em todos os tipos de tabela, exceto InnoDB. See Secção 7.5.12.5, “Como Funciona uma Coluna AUTO_INCREMENT no InnoDB”.

Nota: Só pode haver uma coluna AUTO_INCREMENT por tabela, e ela deve ser indexada e não pode ter uma valor DEFAULT. No MySQL Versão 3.23, uma coluna AUTO_INCREMENT funcionará corretamente apenas se conter apenas valores positivos. Inserir um número negativo é considerado como a inserção de um número positivo muito grande. Isto ocorre para evitar problemaa de precisão quando os números vão de positivo para negativo e também para assegurar que não se obtenha, acidentalmente, uma coluna AUTO_INCREMENT que contenha 0.

Em tabelas MyISAM e BDB você pode especificar colunas AUTO_INCREMENT secundárias em uma chave ulti-coluna. See Secção 3.6.9, “Usando AUTO_INCREMENT”.

Para tornar MySQL compatível com alguns aplicativos ODBC, você pode encontrar o valor AUTO_INCREMENT da última linha inserida com a seguinte consulta:

SELECT * FROM nome_tabela WHERE auto_col IS NULL

Valores NULL são tratados em colunas TIMESTAMP de modo diferente de outros tipos de colunas. Você não pode armazenar um NULL literal em uma coluna TIMESTAMP; definindo a coluna com NULL lhe atribui a a data e a hora atual. Como colunas TIMESTAMP se comportam desta forma, os atributos NULL e NOT NULL não se aplicam de modo normal e são ignorados se você os especificar.

Por outro lado, tornar o uso de colunas TIMESTAMP mais fácil para os clientes MySQL, o servidor relata que tal coluna pode ter o valor NULL atribuído (a que é verdade), mesmo que TIMESTAMP nunca contenham, realmente, um valor NULL. Você pode ver isto quando você utiliza DESCRIBE nome_tabela para obter informações sobre sua tabela.

Note que definir uma coluna TIMESTAMP com 0 não é o mesmo que definí-la com NULL, porque 0 é um valor TIMESTAMP válido.

Um valor padrão (DEFAULT) tem que ser constante, ele não pode ser uma função ou uma expressão.

Se nenhum valor DEFAULT é especificado para uma coluna, o MySQL atribuirá um automaticamente, como a seguir.

Se a coluna aceitar NULL como um valor, o valor padrão é NULL.

Se a coluna é declarada como NOT NULL, o valor padrão depende do tipo de coluna:

Valores padrões devem ser constantes. Isto significa, por exemplo, que você não pode definir o padrão de uma coluna date como o valor de funções como NOW() or CURRENT_DATE.

Um comentário para uma coluna pode ser especificado com a opção COMMENT. O comentário é mostrado pela instrução SHOW CREATE TABLE e por SHOW FULL COLUMNS. Esta opção está disponível a partir do MySQL 4.1. (Ela é perimitida mas ignorada em versões anteriores.)

KEY é normalmente um sinônimo para INDEX. A partir da versão 4.1, o atributo de chave PRIMARY KEY também pode ser especificado apenas como KEY. Isto foi implementado para compatibilidade com outros bancos de dados.

No MySQL,uam chave UNIQUE só pode ter valores distintos. Um erro ocorre se você tantar adicionar uma nova linha com uma chave que coincida com uma já existente.

PRIMARY KEY é uma chave única (KEY) onde todas as colunas chaves devem ser definidas como NOT NULL. Se elas não forem explicitamente declaradas como NOT NULL, isto será feito implicitamente e sem aviso. No MySQL a chave é chamada PRIMARY. Uma tabela pode ter apenas uma PRIMARY KEY. Se você não tiver uma PRIMARY KEY e alguma aplicação perguntar pela PRIMARY KEY em sua tabela, o MySQL retornará a primeira chave UNIQUE, que não possui nenhuma coluna NULL, como a PRIMARY KEY.

Uma PRIMARY KEY pode ser um índice multi-coluna. Porém, você não pode criar um índice multi-coluna usando o atributo de chave PRIMARY KEY em uma especificação de coluna. Fazendo assim apenas colunas simples poderão ser marcadas como primárias. Você deve utilizar uma cláusula PRIMARY KEY(index_nome_coluna, ...) separada.

Um índice UNIQUE é aquele no qual todos os valores no índice devem ser distintos. A exceção a isto é que se for permtido conter valores NULL em uma coluna no índice, ele pode conter múltiplos valores NULL. Este exceção não se aplica a tabelas BDB, que permitem apenas um único NULL.

Se a chave PRIMARY ou UNIQUE consistir de apenas uma coluna e ela é do tipo inteiro, você também poderá se referir a ela como _rowid (novo na versão 3.23.11).

Se você não atribuir um nome ao índice que não é um PRIMARY KEY, ele terá o mesmo nome da prmeira index_nome_coluna, com um sufixo opicional (_2, _3, ...) para torná-lo único. Você pode nome de índices para uma tabela usando SHOW INDEX FROM nome_tabela. See Secção 4.6.8.1, “Recuperando Informações sobre Bancos de Dados, Tabelas, Colunas e Índices”.

Apenas os tipos de tabelas MyISAM, InnoDB, e BDB suportam índices em coluna que possam ter valores NULL. Nos outros casos você deve declarar tais colunas NOT NULL ou um erro será retornado.

Com a sintaxe nome_coluna(length) em uma especificação de índice, você pode criar um índice que utiliza apenas os primeiros length() bytes de uma coluna CHAR ou VARCHAR. Isto pode tornar o arquivo de índices muito menor. See Secção 5.4.4, “Índices de Colunas”.

Apenas os tipos de tabela MyISAM e (a partir do MySQL 4.0.14) InnoDB suportam índice em colunas BLOB e TEXT. Ao colocar um índice em uma coluna BLOB ou TEXT você sempre DEVE especificar o tamanho do índice, até 255 bytes. Por exemplo:

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

Uma especificação index_col_name pode finalizar com ASC ou DESC. Esta palavras chaves são permitidas para estensão futura para especificar o armazenamento do valor do índice em crescente ou decrescente. Atualmente elas são analisadas mas ignoradas; valores de índice são sempre armazenados em ordem crescente.

Quando você utiliza ORDER BY ou GROUP BY com uma coluna TEXT ou BLOB, o servidor ardena valores usando apenas o número inicial de bytes, indicado pela variável do servidor max_sort_length. See Secção 6.2.3.2, “Os Tipos BLOB e TEXT”.

No MySQL Versão 3.23.23 ou posterior, você também pode criar índices FULLTEXT especiais. Eles são usados para busca full-text. Apenas o tipo de tabela MyISAM suporta índices FULLTEXT. Eles só podem ser criados em colunas CHAR, VARCHAR, e TEXT. A indexação sempre ocorre sobre toda a coluna; índices parciais não são suportados. Veja Secção 6.8, “Pesquisa Full-text no MySQL” para detalhes de operação.

No MySQL Versão 3.23.44 ou posterior, tabelas InnoDB suportam verificação de chaves estrangeiras. See Secção 7.5, “Tabelas InnoDB”. Note que a sintaxe FOREIGN KEY no InnoDB é mais restrita que a sintaxe apresentada acima. As colunas da tabela indicada devem ser nomeadas explicitmente. O InnoDB suporta ambas as ações ON DELETE e ON UPDATE em chaves esrtrangiras nos MySQL 3.23.50 e 4.0.8, respectivamente. Veja a seção InnoDB do manual para a sintaxe precisa. See Secção 7.5.5.2, “Restrições FOREIGN KEY”. Para outros tipos de tabelas, MySQL Server analisa as sinatxes FOREIGN KEY, CHECK e REFERENCES no comando CREATE TABLE, mas sem tal ação ser tomada. See Secção 1.8.4.5, “Chaves Estrangeiras”.

Para tabelas ISAM e MyISAM, cada coluna NULL tem um bit extra, arredondado para o byte mais próximo. O tamanho máximo de um registro em bytes pode ser calculado como a seguir:

tamanho da linha = 1
             + (soma do tamanho da coluna)
             + (números de coluna NULL + delete_flag 7)/8
             + (número de colunas de tamanho variável)

delete_flag é 1 para tabelas com formato de registro estático. Tabelas estáticas usam um bit no registro para um parâmetro que indica se o linha foi deletada. delete_flag é 0 para tabelas dinâmicas porque este parâmetro é armazenado no cabeçalho da linha dinâmica.

Estes cálculos não se aplicam à tabelas InnoDB, para a qual o tamanho do armazenamento não é diferente para colunas NULL comparados a colunas NOT NULL.

A opçao opção_tabela e SELECT só são implmentadas no MySQL Versão 3.23 e acima.

A opção TYPE para especificar o tipo de tabela possui os seguintes valores:

See Capítulo 7, Tipos de Tabela do MySQL.

Se um tipo de tabela é especificado, e este tipo não está disponível, MySQL irá usar MyISAM. Por exemplo, se uma definição de tabela inclui a opção TYPE=BDB mas o MySQL não suporta tabelas BDB, a tabela será criada como uma tabela MyISAM. Isto torna possível de se ter uma configuração de replicação onde você tem tabelas transacionaisno master mas as tabelas criadas no slave são não transacionais (para obter mais velocidade). No MySQL 4.1.1 você obtém um aviso se o tipo de tabela especificado não é aceito.

Os outros tipos de tabelas são utilizados para otimizar o comportamento da tabela. Na maioria dos casos, você não precisa especificar nenhuma delas. As opções funcionam com todos os tipos, a menos que haja indicação:

Quando você utiliza uma tabela MyISAM, MySQL usa o produto de MAX_ROWS * AVG_ROW_LENGTH para decidir o tamanho da tabela resultante. Se você não especificar qualquer uma das opções acima, o tamanho máximo de uma tabela será 4G (ou 2G se o seu sistema operacional só suporta tabelas de 2G). A razão para isto é apenas manter o tamanho dos ponteiros baixo para tornar o índice menor e mais rápido se você realmente não precisa de tabelas grandes.

Se você não utilizar PACK_KEYS, o padrão é só empacotar strings, não números. Se você utilizar PACK_KEYS=1, números também serão empacotados.

Ao empacotar chaves numéricas binárias, o MySQL usará a compactação prefixada. Isto significa que você só terá grandes benefícios disto se você tiver muitos números iguais. Compactação prefixada significa que toda a chave precisa de um byte extra para indicar quantos bytes das caves anteriores são o mesmo da próxima chave (note que o ponteiro para a linha é armazenado na ordem do byte mais alto em primeiro diretamente depois da chave, para aumentar compactação). Isto significa que se você tiver muitas chaves iguais em duas linhas consecutivas, todas os chaves ``iguais'' seguintes irão normalmente ter apenas 2 bytes (incluindo o ponteiro para a linha). Compare isto isto ao caso comum onde as chaves seguintes irão levar tamanho_armazenamento_chave + tamanho_ponteiro (nomralmente 4). Por outro lado, se todas as chaves são totalmente diferente, você usará 1 byte por chave, se a chave não puder ter valores NULL. (Neste caso o tamanho da chave empacotada será armazenado no mesmo byte que é usado para marcar se a chave é NULL.)

No MySQL 3.23, Se você especificar um SELECT depois de uma instrução CREATE, MySQL criará novos campos para todos os elemento em SELECT. Por exemplo:

mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT,
    ->        PRIMARY KEY (a), KEY(b))
    ->        TYPE=MyISAM SELECT b,c FROM test2;

Isto irá criar uma tabela MyISAM com três colunas, a, b e c. Note que as colunas da instrução SELECT são inseridas do lado correto da tabela, nào sobreposta nela. Considere o seguinte exemplo:

mysql> SELECT * FROM foo;
+---+
| n |
+---+
| 1 |
+---+

mysql> CREATE TABLE bar (m INT) SELECT n FROM foo;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM bar;
+------+---+
| m    | n |
+------+---+
| NULL | 1 |
+------+---+
1 row in set (0.00 sec)

Para cada linha na tabela foo, uma linha é inserida em bar com os valores de foo e os valores padrões para a nova coluna.

CREATE TABLE ... SELECT não irá criar automaticamente nenhum índice para você. Isto é feito intencionalmente para deixar o comando o mais flexível possível. Se você quiser ter índices em uma tabela criada, você deve especificá-lo antes da instrução SELECT:

mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;

Se ocorrer qualquer erro durante enquanto os dados são copiados para a tabela, ele será automaticamente deletado.

Você pode preceder o SELECT por IGNORE ou REPLACE para indicar como tratar registros que duplicam valores de chave única. Com IGNORE, novos registros que duplicam um registro existente em um valor de chave única são descartados. Com REPLACE, novos registros substituem registros que tem o mesmo valor de chave única. Se nem IGNORE nem REPLACE são especificados, valir de chave unica duplicados resultam em erro.

Para assegurar que o log binário/atualização pode ser usado para recriar a tabela original, MySQL não permitirá inserções concorrentes durante um CREATE TABLE ... SELECT.

A opção RAID_TYPE irá ajudá-lo a exceder o limite de 2G/4G limit para arquivo de dados MyISAM (não o arquivo de índice) em sistemas operacionais que não suportam arquivos grandes. Note que esta opção não é recomendada para sistema de arquivos que suportam arquivos grandes!

Você pode obter mais velocidade da gargalo de E/S colocando diretorios RAID em diferentes discos físicos. RAID_TYPE funcionará em qualquer sistema operacional, desde que você tenha configurado o MySQL com --with-raid. Por agora o único RAID_TYPE permitido é STRIPED (1 e RAID0 são utilizados para isto).

Se você especificar RAID_TYPE=STRIPED para tabeals MyISAM, MyISAM criará subdiretórios RAID_CHUNKS chamados 00, 01, 02 no diretório de banco de dados. Em cada um destes diretórios MyISAM criará uma nome_tabela.MYD. Ao escrever dados no arquivo de dados, o manipulador RAID irá mapear o primeiro RAID_CHUNKSIZE *1024 bytes para o primeiro arquivo e os próximos RAID_CHUNKSIZE *1024 bytes para o próximo arquivo.

UNION é utilizado quando você quer utilizar uma coleção de tabelas identicas como uma. Isto só funciona com tabelas MERGE. See Secção 7.2, “Tabelas MERGE”.

No momento você precisa ter privilégios SELECT, UPDATE e DELETE nas tabelas mapeadas para uma tabela MERGE. Todas as tabelas mapeadas devem estar no mesmo banco de dados na tabela MERGE.

Se você quiser inserir dados em uma tabela MERGE, você tem que especificar com INSERT_METHOD na tabela onde o registro deve ser inserido. INSERT_METHOD é uma opção útil somente para tabelas MERGE. See Secção 7.2, “Tabelas MERGE”. Esta opção foi introduzida no MySQL 4.0.0.

Na tabela criada a chave PRIMARY será colocado primeiro, seguida de todas a chaves únicas (UNIQUE) e então das chaves normais. Isto ajuda o otimizador MySQL para priorizar qual chave utilizar e também a detectaa mais rapidamente chaves únicas (UNIQUE) duplicadas.

Utilizando DATA DIRECTORY='directorio' ou INDEX DIRECTORY='directorio' você pode especificar onde o mecanismo de armazenamento deve colocar os seus arquivos de tabelas e índices. Note que ``diretório'' deve ser um caminho completo para o diretório (não um caminho relativo).

Isto só funciona para tabelas MyISAM no MySQL 4.0, quando não estiver usando a opção --skip-symlink. See Secção 5.6.1.2, “Utilizando Links Simbólicos para Tabelas”.