Artigos de Banco de Dados.



Back-up rápido de tabelas MySQL

Uma boa prática  antes de atualizar a sua base é sempre fazer um back-up das tabelas que pode ser feito de forma rápida com o seguinte comando:

// criando a nova tabela
CREATE TABLE nova_tabela LIKE minha_base.tabela; 
// copiando os dados

INSERT nova_tabela SELECT * FROM minha_base.tabela;

CHAR ou VARCHAR? Que tipo de campo escolher no MySQL?

Ambos os tipos de campo “texto” podem ser definidos com um comprimento máximo no MySQL. A principal diferença entre os dois tipos de campos é que o CHAR sempre armazenará o texto com o tamanho fixo, mesmo que o “texto” seja menor do que o tamanho máximo especificado na criação do campo. Neste caso, o próprio SGBD do MySQL irá usar espaços a direita do “texto” preenchendo o campo até a quantidade máxima especificada em sua criação. Note, que esses espaços serão removidos quando você recuperar os dados a armazenados.

Por outro lado, “textos” armazenadas em uma coluna VARCHAR exigem apenas o tamanho do “texto” armazenado. Portanto, a palavra “ivo” em uma coluna VARCHAR(10) irá requerer quatro bytes de espaço (o comprimento do “texto” mais 1), enquanto em uma coluna CHAR(10), a mesma palavra exigirá 10 bytes de espaço.

Assim, de modo geral, as colunas VARCHAR tendem a consumir menos espaço em disco do que colunas CHAR. Contudo, as bases de dados são normalmente mais rápidas quando se trabalha com colunas de tamanho fixo, o que é um argumento a favor de CHAR. E essa mesma palavra de três letras, “ivo”, em um CHAR(3) utiliza apenas 3 bytes, enquanto em um VARCHAR(10) requer 4. Desta forma, como decidir qual usar?

Se o “texto” for sempre de um tamanho determinado (por exemplo, uma abreviatura do estado), use CHAR, caso contrário, use VARCHAR. Você pode notar, porém, que, em alguns casos, o MySQL define uma coluna de um tipo (como CHAR) embora você a tenha criado como outro tipo (VARCHAR). Isto é perfeitamente normal e é a maneira que o MySQL possui para melhorar o desempenho.

OBS: Note que a palavra texto encontra-se marcada aqui no sentido de ressaltar que o campo “string” pode, além de conter caracteres, conter também números, datas ou listas armazenadas como texto (string). Neste caso, não poderão ser usadas as funções SQL de cálculo específicas de outros tipos. A não ser que sejam convertidas antes.

Fonte: PHP 6 and MySQL 5 for Dynamic Web Sites. ULLMAN, Larry, 2007.

Tipos de Campos no MySQL, saiba como escolher o tipo correto

Este post tem o objetivo de ajudar o desenvolvedor na escolha adequada do tipo de campo que deverá utilizar no banco MySQL para persistir seus dados.

O desenvolvedor inexperiente costuma confundir bastante os tipos de campo da linguagem utilizada (PHP por exemplo) com os tipos que o banco pode armazenar. Um exemplo clássico dessa confusão é o booleano que é automaticamente convertido de true para 1 e false para 0 (zero).  Além disso, há o clássico erro de armazenamento de CPF em campo numérico o que faz com que todos os zeros a esquerda se percam.

Portanto coloco abaixo a tabela com os campos do MySQL para que sirva de um guia de implementação do banco de dados de sua aplicação.

Lembro ainda que antes de mais nada você deve identificar qual o tipo de variável que irá armazenar: número, texto, binário, data,  lista ou combinação destes. Em seguida estimar o tamanho do campo e se será variável ou fixo. O MySQL costuma preencher campos texto não variáveis com espaços em branco. Em seguida, verificar se permitirá nulo ou não, se é uma chave candidata, primária, estrangeira, índice ou único.

Não há uma regra fixa para saber como os bancos deverão ser implementados, mas tenha sempre em mente que neste caso, mais é melhor. Ou seja, é melhor sobrar espaço do que ter uma informação inserida com pedaços faltando. Já viu alguém nascer no “Rio de Jane” pois o desenvolvedor não colocou o campo com o tamanho correto?!

Observações:

  • O tamanho do campo para tipos numéricos não afeta o intervalo de valores que pode ser armazenado na coluna. Colunas definidas como TINYINT (1) ou TINYINT (20) podem armazenar exatamente, os mesmos valores. Em vez disso, para inteiros, o tamanho determina a largura do campo, para casas decimais, o tamanho é o número total de dígitos que pode ser armazenado.
  • Muitos dos tipos de dados têm nomes sinônimos: INT e INTEGER, DEC e DECIMAL, etc
  • O tipo de campo TIMESTAMP é automaticamente definido com a data e hora quando um INSERT ou UPDATE ocorre, mesmo se nenhum valor for especificado para esse campo . Se uma tabela possui múltiplas colunas TIMESTAMP, somente a primeira será atualizada quando um INSERT ou UPDATE é realizado.
  • MySQL também tem diversas variantes para os tipos de texto que permitem o armazenamento de dados binários. Estes tipos são BINARY, VARBINARY, TINYBLOB, MEDIUMBLOB, e LONGBLOB. Esses tipos são usados para armazenamento de arquivos ou dados criptografados.
Tipos Numéricos
Tipo Uso Tamanho
Atributo MIN MAX
TINYINT Um inteiro muito pequeno Signed: -128 127
Unsigned 0 255
SMALLINT Um inteiro pequeno Signed: –32768 32767
Unsigned 0 65535
MEDIUMINT Um inteiro de tamanho mediano Signed: –8388608 8388607
Unsigned 0 16777215
INT or INTEGER Um inteiro de tamanho normal Signed: –2147483648 2147483647
Unsigned 0 4294967295
BIGINT Um inteiro de temanho grande Signed: –9223372036854775808 9223372036854775807
Unsigned 0 18446744073709551615
FLOAT Um pequeno número de ponto flutuante (precisão simples) Signed –3.402823466E+38 –1.175494351E-38, 0
1.175494351E-38 3.402823466E+38
Não pode ser unsigned -
OBS Se o número de decimais não for especificado ou for <= 24 será de precisão simples
DOUBLE,
DOUBLE PRECISION,
REAL
Um número de ponto flutuante de tamanho normal (precisão dupla) Signed -1.7976931348623157E+308 -2.2250738585072014E-308, 0
2.2250738585072014E-308 1.7976931348623157E+308
Não pode ser unsigned -
OBS Se o número de decimais não for especificado ou for 25 <= Decimals <= 53 será de precisão dupla
DECIMAL,
NUMERIC
Um número de ponto flutuante descompactado . Signed Se comporta como um campo CHAR: “descompactado” significa que o número é armazenado como uma string, usando um caractere para cada dígito do valor. O ponto decimal e, para números negativos, o sinal ‘-’ não é contado. Se o decimal for 0, os valores não terão ponto decimal ou parte fracionária. O alcance máximo de valores decimais é o mesmo que para o DOUBLE, mas a faixa atual para um campo DECIMAL dado pode ser limitado pela escolha de comprimento e decimais.
Não pode ser unsigned -
OBS Se Decimais é deixado de fora ele é definido como 0. Se o comprimento é deixado de fora ele é definido como 10. Note que no MySQL 3,22 o comprimento inclui o sinal eo ponto decimal
Campos de Datas
Formato MIN MAX
DATE Data ‘1000-01-01’ ‘9999-12-31’
OBS Formato: ‘YYYY-MM-DD’
DATETIME Data e horário ‘1000-01-01 00:00:00’ ‘9999-12-31 23:59:59’
OBS Formato: ‘YYYY-MM-DD HH:MM:SS’
TIMESTAMP Timestamp ‘1970-01-01 00:00:00’ aproximadamente 2037
OBS Formato: YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD ou YYMMDD, dependendo se M é 14 (ausente), 12, 8 ou 6, podendo ser strings ou números.

Este tipo é recomendável para instruções de INSERT ou UPDATE pois é automaticamente marcado com os valores da operação mais recente quando não informado.

TIME A time ‘-838:59:59’ ‘838:59:59’
OBS formato: ‘HH:MM:SS’, podem ser strings ou números
YEAR Anos com 2 ou 4 digitos. O padrão é 4 digitos 4 digitos 1901 2155 e 0000
2 digitos 1970 2069
OBS Formato: YYYY
podem ser strings ou números.
Campos Texto
MIN MAX
CHAR String de tamanho fixo. Sempre é completada com espaços a direita até o tamanho definido 1 255 caracteres
OBS Espaços excessivos são removidos quando o valor é trazido.Os valores são ordenados e comparados ignorando caixas altas e baixas de acordo com a codificação padrão, a menos que seja fornecido uma chave binária.
VARCHAR String de tamanho variável 1 255 caracteres
OBS Os valores são ordenados e comparados ignorando caixas altas e baixas de acordo com a codificação padrão, a menos que seja fornecido uma chave binária.Nota: Espaços execessivos são removidos quando o valor é inserido.
TINYTEXT 0 255 (2^8 – 1) caracteres
TEXT 0 65535 (2^16 – 1) caracteres
MEDIUMTEXT 0 16777215 (2^24 – 1) caracteres
LONGTEXT 0 4294967295 (2^32 – 1) caracteres
Dados Binários
TINYBLOB 0 255 (2^8 – 1) caracteres
BLOB 0 65535 (2^16 – 1) caracteres
MEDIUMBLOB 0 16777215 (2^24 – 1) caracteres
LONGBLOB 0 4294967295 (2^32 – 1) caracteres
Listas
MIN MAX
ENUM Enumeração String que pode conter apenas um valor ou zero 65535 valores distintos.
SET Lista String que pode conter zero ou mais valores 64 itens
fonte: http://help.scibit.com/Mascon/masconMySQL_Field_Types.html e http://dev.mysql.com/doc/refman/5.0/en/data-type-overview.html(tradução livre)

Nota:

Assine o RSS de meus Artigos e Comentários.