MySQL

Estruturas básicas de sintaxe para criação de funções e procedures

Comentários em mysql

-- Este é um comentário de linha única
# Este também é um comentário de linha única

/* Este é um comentário
   de múltiplas linhas */
SELECT * FROM tabela;

Declaração de variáveis

Variáveis definidas pelo usuário: São identificadas por um símbolo @ usado como prefixo. Você pode inicializar, atribuir e usar essas variáveis em qualquer lugar do seu código. Por exemplo:

SET @x = 10;
SELECT @x + 5;

Variáveis locais: São declaradas dentro de um bloco BEGIN … END usando o comando DECLARE. Elas só podem ser usadas dentro desse bloco e são automaticamente destruídas quando o bloco termina. Por exemplo:

BEGIN
  DECLARE x INT DEFAULT 10;
  SELECT x + 5;
END;

Variáveis de sistema: São definidas pelo MySQL e armazenam informações sobre o servidor, a sessão ou o status do banco de dados. Elas são identificadas por um prefixo @@ e podem ser lidas ou modificadas usando os comandos SET ou SHOW. Por exemplo:

SHOW VARIABLES LIKE 'version';
SET @@autocommit = 0;

Atribuindo o retorto de uma consulta a uma tabela a uma variavel

SELECT coluna INTO @variavel FROM tabela WHERE condicao LIMIT 1;

Arrays

O MySQL não suporta arrays, mas é possível simular arrays usando variáveis de usuário mas eles não são muito elegantes ou eficientes.

1 - Pode-se usar uma string com valores separados por vírgula (ou outro caractere) e usar as funções FIND_IN_SET ou SUBSTRING_INDEX para acessar os elementos. Exemplo:

SET @myArray = '2,5,2,23,6';
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@myArray, ',', 3), ',', -1); -- retorna 2

2 - Podemos usar uma tabela temporária com uma coluna de índice e uma coluna de valor e usar o comando SELECT para acessar os elementos. Exemplo:

CREATE TEMPORARY TABLE myArray (index INT, value INT);
INSERT INTO myArray VALUES (1, 2), (2, 5), (3, 2), (4, 23), (5, 6);
SELECT value FROM myArray WHERE index = 3; -- retorna 2

3 - Podemos e usar uma tabela associativa usando o tipo TABLE OF e um índice numérico ou de string. Esse recurso está disponível apenas dentro de blocos PL/SQL e requer a declaração de um tipo de tabela. Exemplo:

DECLARE
  TYPE myArrayType IS TABLE OF INT INDEX BY PLS_INTEGER;
  myArray myArrayType;
BEGIN
  myArray(1) := 2;
  myArray(2) := 5;
  myArray(3) := 2;
  myArray(4) := 23;
  myArray(5) := 6;
  SELECT myArray(3); -- retorna 2
END;

IF em mysql

Usando em funções e procedures:

IF condição THEN
  -- bloco de código 1
ELSEIF condição THEN
  -- bloco de código 2
...
ELSE
  -- bloco de código n
END IF;

Usando em comandos SQL:

SELECT
  IF(condição, valor_se_verdadeiro, valor_se_falso) AS nome_da_coluna
FROM tabela;

CASE em mysql

Usando em funções e procedures:

CASE
  WHEN condição THEN
    -- bloco de código 1
  WHEN condição THEN
    -- bloco de código 2
  ...
  ELSE
    -- bloco de código n
END CASE;

Usando em comandos SQL:

SELECT
  CASE
    WHEN condição THEN valor_se_verdadeiro
    WHEN condição THEN valor_se_verdadeiro
    ...
    ELSE valor_se_falso
  END AS nome_da_coluna
FROM tabela;

WHILE em mysql

O <rotulo> é um identificador opcional que serve para referenciar o loop na declaração LEAVE ou ITERATE. A condicao é uma expressão que pode ser avaliada como verdadeira ou falsa.

[<rotulo>:] WHILE condicao DO
  -- bloco de código
END WHILE [<rotulo>];

REPEAT em mysql

[<rotulo>:] REPEAT
  -- bloco de código
UNTIL condicao
END REPEAT [<rotulo>];

LOOP em mysql

[<rotulo>:] LOOP
  -- bloco de código
  IF condicao THEN
    LEAVE <rotulo>;
  END IF;
END LOOP [<rotulo>];

FOR em mysql

FOR loop_counter IN [REVERSE] valor_inicial .. valor_final DO
  -- bloco de código
END FOR;

Declarando funções e procedures em mysql

Criando uma função

CREATE FUNCTION nome_da_funcao (parametro1 tipo, parametro2 tipo, ...) RETURNS tipo
BEGIN
  -- bloco de código
END;

Modificação do delimitador na criação de funções

Dependendo da complexidade da função é possível que seja necessário definir o delimitador de comandos para que o mysql não interprete o ponto e vírgula como o fim da declaração da função.

DELIMITER //
CREATE FUNCTION comparar (n INT, m INT)
RETURNS VARCHAR(20)
BEGIN
  DECLARE msg VARCHAR(20);
  IF n > m THEN
    SET msg = 'maior que';
  ELSEIF n = m THEN
    SET msg = 'igual a';
  ELSE
    SET msg = 'menor que';
  END IF;
  SET msg = CONCAT(n, ' é ', msg, ' ', m);
  RETURN msg;
END //
DELIMITER ;

Chamando uma função

SELECT Comparar(10, 5); -- retorna '10 é maior que 5'
SELECT Comparar(5, 5); -- retorna '5 é igual a 5'
SELECT Comparar(5, 10); -- retorna '5 é menor que 10'

Criando uma procedure

CREATE PROCEDURE nome_da_procedure (parametro1 tipo, parametro2 tipo, ...)
BEGIN
  -- bloco de código
END;

mysqldump

O mysqldump é uma ferramenta muito útil para backup de banco de dados mysql. Mas é preciso se atentar para algumas questões no uso dessa ferramenta.

Sintaxe básica

A sintaxe padrão para uso do mysqldump é:

mysqldump -u [usuario] [banco] [tabela]

Se a tabela for omitida será feito o dump de todo o banco de dados. Para realizar o dump de todos os bancos de dados existentes no servidor basta incluir na linha de comando --all-databases.

Para automatização de scripts para rotinas de backup do banco de dados pode ser criado um usuário para essa finalidade, com permissões de leitura do banco e que possa fazer login a partir do localhost sem a necessidade de senha. Caso o usuário em questão possua senha é necessário incluir no comando -p.

Por padrão o mysqldump irá exibir o resultado na tela, sendo muito comum direcionar a saida para um arquivo acrescentando > arquivo.sql ao final do comando.

Salvamento o resultado em um arquivo

Embora seja muito comum, o salvamento usando > arquivo.sql não é uma boa prática, principalmente quando se envolve a cópia do arquivo entre sistemas operacionais diferentes, esse uso pode fazer com que o arquivo resultante apresente dados corrompidos tais como problemas de acentuação.

Para evitar que haja problemas de acentuação com o uso do mysqldump ou qualquer outra corrupção de dados, é recomendado salvar o arquivo usando a opção --result-file=arquivo.sql ou -r arquivo.sql.

Exemplo para salvar arquivo com nome backup.sql:

mysqldump -u [usuario] [banco] -r backup.sql

Desabilitando o trancamento de tabelas

Em alguns cenários de uso pode ser necessário evitar que o mysqldump inclua no arquivo as chamadas para trancar e destrancar as tabelas com lock e unlock.

Arquivos contendo lock tables e que por algum motivo não consigam ser processados por completo podem deixar as tabelas trancadas e causar dor de cabeças na hora de acessar o servidor.

Por exemplo: se a única forma de acesso ao servidor for via phpMyAdmin e as tabelas estiverem trancadas o phpMyAdmin pode travar e não funcionar corretamente.

Para desativar o trancamento de tabelas basta acrescentar a linha de comando a opção --skip-add-locks.

mysqldump -u [usuario] [banco] --skip-add-locks -r backup.sql

Replace em vez de insert

Para se restaurar um banco de dados que contenha triggers que realizem inserções em outras tabelas pode gerar problemas na importação do arquivo de dump em decorrência de chaves duplicadas.

Em um cenário de restauração completa onde o banco de dados está vazio uma alternativa para fazê-lo poderia ser remover as triggers que se enquadram nessa situação.

Mas considerando que há cenários que não seja possível desativar as triggers o ideal é que o arquivo de dump tenha sido criado com a opção --replace pois nesse caso o mysqldump irá gerar as linhas de inserção com o comando REPLACE em vez do INSERT.

mysqldump -u [usuario] [banco] --replace -r backup.sql

Trabalhando com colunas binárias

Se o banco em questão possuir colunas binárias, tais como uuid. É importantíssimo o uso da opção --hex-blob a fim garantir que os dados binários sejam gravados de maneira correta.

mysqldump -u [usuario] [banco] --hex-blob -r backup.sql

Definindo o charset

Em alguns casos abrir o arquivo exportado pelo mysqldump no workbench pode apresentar uma mensagem reclamando do charset do arquivo. Conforme imagem abaixo:

Erro quando o workbench não consegue identificar o charset do arquivo

Uma das maneiras de contornar esse problema seria definir o charset na geração do arquivo no mysqldump através da opção --default-character-set=[charset].

Exemplo, para salvar o arquivo com o charset latin1:

mysqldump -u [usuario] [banco] --default-character-set=latin1 -r backup.sql

Exportando somente os dados sem a estrutura de tabelas

Para exportar somente o DML, sem a estrutura das tabelas (DDL), é recomendável a combinação das opções --no-create-info e --skip-triggers caso exista alguma tabela com triggers.

Combinando opções

Todas as opções disponíveis para linha de comando do mysqldump podem ser combinadas em qualquer ordem. Exemplo:

mysqldump -u [usuario] --no-create-info --skip-triggers [banco] --default-character-set=latin1 --hex-blob --replace --skip-add-locks -r [arquivo]

Para uma lista de todas as opções disponíveis e seu uso, consulte a documentação oficial do mysqldump.

Trabalhando com nomes de tabela em minúsculo

É possível configurar o servidor mysql para utilizar todos os caracteres minúsculos como nome das tabelas, para isso é necessário editar o arquivo de configuração my.cnf e incluir a linha abaixo:

lower_case_table_names = 1

Veja também

Renato Monteiro Batista
Renato Monteiro Batista
Engenheiro de Computação
https://renato.ovh
     		Renato Monteiro Batista
2019-2024 RMB Informática | rmbinformatica.com | Loja Virtual | Suporte à Clientes

resultados encontrados ""

    Nenhum resultado encontrado ""