Arquivo da categoria: MySQL

Como obter o próximo valor de um campo auto-incremental no mysql

Quem trabalha com o Oracle PL/SQL sabe que você pode obter isso através do NextVal() da seqüência, mas como fazer isso no MySQL?

Eu estava tentando gerar um código Hash para ser utilizado como uma referência externamente a um documento – para não exibir o ID interno usado na tabela -, mas não queria utilizar duas consultas (Um insert, e um select para obter o valor inserido).

Para resolver este problema em uma consulta apenas, criei uma função no banco de dados que me retornaria este valor:


CREATE FUNCTION `getAutoincrementalNextVal`(`TableName` VARCHAR(50))
	RETURNS BIGINT
	LANGUAGE SQL
	NOT DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY DEFINER
	COMMENT ''
BEGIN

DECLARE Value BIGINT;

SELECT
	AUTO_INCREMENT INTO Value
FROM
	information_schema.tables
WHERE
	table_name = TableName AND
	table_schema = DATABASE();

RETURN Value;

END

Para utilizar esta função, basta passar como parâmetro o nome da tabela que se deseja obter o valor do próximo ID (campo auto-incremental):

SELECT getAutoincrementalNextval ('document')

No meu código, para gerar o Hash para o documento, fiz da seguinte forma:

INSERT INTO
    document (Code, Title, Body)
VALUES ( 				
    sha1( concat (convert (now(), char), ' ', getAutoincrementalNextval ('document') ) ),
    'Title',
    'Body'
);

Este código hash foi apenas uma forma de não expor ao usuário o código interno de meu documento. Para gerar um código único eu crio uma pré-imagem SHA-1 baseada na junção da hora atual e o ID do registro.

Natural de Salvador-BA, é graduado em Analise de Sistemas pela Universidade Católica do Salvador (UCSal, 2003), e Especialista em Engenharia de Software pela Universidade Salvador (2010).

Como saber onde o MySQL está armazenando os dados de seus bancos de dados?

Depois de uma atualização desastrosa no Ubuntu, o MySQL não consegue mais iniciar. Então, a única forma de resolver este problema é reinstalando o linux. Mas como salvar as informações armazenadas no banco de dados se não é possível inicializar o servidor MySQL para fazer o backup dos dados? Bem, as sugestões encontradas na Internet é para que seja feito o backup dos dados (copiando os arquivos datafiles) e depois sobrescrevessem os dados de uma nova instalação.

Mas onde estão os dados do MySQL?

A solução é simples:

Encontre o arquivo “my.cnf” (ou my.ini, se você estiver usando o Windows), utilizando o comando find do linux:

find / -name my.cnf

Em servidores que utilizam a configuração padrão, este arquivo se encontra na pasta “/etc/mysql/my.cnf”. Visualize o conteúdo deste arquivo, utilizando o comando cat:

cat /etc/mysql/my.cnf

Dentro do conteúdo deste arquivo, procure a variável “datadir”, o valor atribuído a ela é onde se localiza os dados armazenados (ex: /var/lib/mysql).

Pronto! Para copiar o arquivo é simples, modifique as permissões desta pasta inteira (recursivamente), compacte-a e mova para a pasta que você consiga acessar pelo cliente FTP (por exemplo, a pasta padrão do servidor web). Mude a permissão deste arquivo para conseguir copiar os dados pelo FTP.

chmod -R 777 /var/lib/mysql
tar -zcvf /var/lib/mysqldata.tar.gz /var/lib/mysql
mv /var/lib/mysqldata.tar.gz /var/www
chmod 777 /var/www/mysqldata.tar.gz

Agora, caso deseje restaurar o banco de dados é só seguir os seguintes passos:

  • Instale um novo servidor MySQL e teste para saber se ele está inicializando
  • Crie no novo servidor o banco de dados desejado (ele deve ter o nome exato do banco de dados antigo
  • Pare o servidor do MySQL
  • Sobrescreva a pasta que o MySQL criou para este banco utilizando a pasta contida dentro do arquivo de backup.
  • Inicialize o novo servidor MySQL
  • Banco de dados restaurado!
Natural de Salvador-BA, é graduado em Analise de Sistemas pela Universidade Católica do Salvador (UCSal, 2003), e Especialista em Engenharia de Software pela Universidade Salvador (2010).

Como obter registros aleatórios no MySQL

Conseguir obter um registro aleatório pode até não ser um dos recursos mais importantes de um banco de dados, mas não deixa de ser muito útil, principalmente se você precisa:

  • Exibir itens sem mostrar favoritismo;
  • Testar o código retornando valores diferentes;
  • Exibir itens de forma não-específica;

Porém, apesar da grande utilidade, este é um recurso muito fácil de ser utilizado. Para isso basta utilizar o comando Rand() na cláusula ORDER BY:

 SELECT artigo_id, titulo, descricao FROM artigo ORDER BY RAND() LIMIT 4 

O código acima retornará a informação artigo_id, titulo, descricao (SELECT artigo_id, titulo, descricao) de quatro artigos (LIMIT 4) que estão contidos na tabela artigo (FROM artigo) selecionados de forma aleatória (ORDER BY RAND())

Natural de Salvador-BA, é graduado em Analise de Sistemas pela Universidade Católica do Salvador (UCSal, 2003), e Especialista em Engenharia de Software pela Universidade Salvador (2010).

Comando num_rows do MySQLi (prepared statement) retorna apenas o valor 0

Continuando os meus trabalhos de conversão de um código que utilizava a conexão MySQl para a conexão MySQLi encontrei um novo problema: o comando num_rows sempre retornava o valor 0, mesmo nas consultas em que eu sabia que traziam mais resultados:

$lobj_mysqliQuery = $pobj_DBObject->stmt_init();
$lobj_mysqliQuery->prepare("SELECT id FROM usuario WHERE nome like ?");
$lobj_mysqliQuery->bind_param("s", $plstr_usuarioNome);
$lobj_mysqliQuery->execute();

$llng_usuarioQuantidade = $lobj_mysqliQuery->num_rows;

Bem, onde estava o erro? Aparentemente (novamente) estava tudo correto. Porém, o MySQLi tem uma forma diferente de ver a vida…:)

O erro estava na ausência da utilização do comando store_result(). Que, segundo o manual do PHP, serve para transferir o resultado da última consulta (Resultset) para a variável desejada. Assim, como não havíamos executado este comando, era como se estivéssemos tentando pegar o número de linhas de um ResultSet vazio.

o código final ficou assim, e funcionou:

$lobj_mysqliQuery = $pobj_DBObject->stmt_init();
$lobj_mysqliQuery->prepare("SELECT id FROM usuario WHERE nome like ?");
$lobj_mysqliQuery->bind_param("s", $plstr_usuarioNome);
$lobj_mysqliQuery->execute();
$lobj_mysqliQuery->store_result();

$llng_usuarioQuantidade = $lobj_mysqliQuery->num_rows;

* Observação: Nestes exemplos estão ocultos os comandos responsáveis por abrir e fechar a conexão com o banco de dados.

Natural de Salvador-BA, é graduado em Analise de Sistemas pela Universidade Católica do Salvador (UCSal, 2003), e Especialista em Engenharia de Software pela Universidade Salvador (2010).

Warning: mysqli_connect() [function.mysqli-connect]: (HY000/2005): Unknown MySQL server host ‘localhost:3306’ (11004)

Ao tentar migrar um sistema PHP das conexões MySQL para MySQLi, me deparei com o seguinte problema:

Warning: mysqli_connect() [function.mysqli-connect]: (HY000/2005): Unknown MySQL server host ‘localhost:3306’ (11004)

Aparentemente não havia erro:

$lobj_Database = mysqli_connect(SETTINGS_SGBD_HOST.":".SETTINGS_SGBD_PORT, SETTINGS_SGBD_USER, SETTINGS_SGBD_PASSWORD, SETTINGS_SGBD_DATABASE);

Aparentemente, pois não me lembrei que a sintaxe do MySQLi é um pouco diferente. O endereço da porta passa a ser o último parâmetro da conexão. Ficando assim:

$lobj_Database = mysqli_connect(SETTINGS_SGBD_HOST, SETTINGS_SGBD_USER, SETTINGS_SGBD_PASSWORD, SETTINGS_SGBD_DATABASE, SETTINGS_SGBD_PORT);

Importante lembrar que este é um método de conexão estilo procedural, e que o MySQLi suporta o estilo orientado a objeto também.

Natural de Salvador-BA, é graduado em Analise de Sistemas pela Universidade Católica do Salvador (UCSal, 2003), e Especialista em Engenharia de Software pela Universidade Salvador (2010).

Como reinicializar o MySQL no Linux Ubuntu

Depois de editar qualquer arquivo de configuração do MySQL não conseguimos observar as modificações funcionando pois é necessário que o mesmo seja reinicializado para que as novas configurações entrem em vigor. Para fazer isso, no Ubuntu, digite na linha de comando:

/etc/init.d/mysql start
/etc/init.d/mysql stop

Ou somente:

/etc/init.d/mysql restart

Se os comandos não funcionarem, tente navegar através dos diretório e então reinicie o servidor:

cd 
cd /etc/init.d
mysql restart
Natural de Salvador-BA, é graduado em Analise de Sistemas pela Universidade Católica do Salvador (UCSal, 2003), e Especialista em Engenharia de Software pela Universidade Salvador (2010).

MySQL: Como copio a tabela de um banco de dados para outro?

Estava precisando criar um outro banco de dados para trabalhar com uma nova versão de um sistema, e me deparei com uma dúvida: como faço para copiar a tabela de um banco de dados para outro banco de dados em um mesmo servidor?

A resposta é simples! Digamos que você tenha dois bancos “BancoAntigo” e “BancoNovo”, e você deseja copiar a tabela “Teste” do “BancoAntigo” para o “BancNovo”:

DROP TABLE IF EXISTS BancoNovo.Teste;
CREATE TABLE BancoNovo.Teste SELECT * FROM BancoAntigo.Teste;
Natural de Salvador-BA, é graduado em Analise de Sistemas pela Universidade Católica do Salvador (UCSal, 2003), e Especialista em Engenharia de Software pela Universidade Salvador (2010).

Problema de acentuação no MySQL: Latin1 x UTF8

Um cliente me forneceu o backup de seu banco de dados MySQL, porém, ao importá-lo alguns caracteres estranhos substituíam os esperados acentos. Palavra com “Olá” estava escrita como “Olá”, “Termos de Serviço” estava como “Termos de Serviço”.

O Problema obviamente era de codificação (charset, collation) utilizada entre os dois bancos: o Banco original estava no padrão Latin 1 (latin_swedish_ci) enquanto o outro estava em UTF8 (utf8_general_ci). Caracteres como “ç”, “ã”, “á” e etc pertence ao padrão Latin 1 (ISO-8859-1); o padrão UTF8 por utilizar menos dados para armazenar cada caractere é obrigado a representar as letras acentuadas (e a cedilha) utilizando dois caracteres, o que vem a causar este problema.

A solução óbvia para esta falha seria modificar o charset do banco de dados e da tabela. Mas isto não solucionou o meu caso. Resolvi então utilizar uma abordagem menos rebuscada: criei um script PHP para atualizar cada linha na tabela do banco de dados convertendo-as para o padrão desejado.

Para isso utilizei a função mb_convert_encoding:

<?php

	set_time_limit(0); // Tempo máximo de execução: sem limite (0).

	// ----------------------------------------------------------------------
	// Configurações
	// ----------------------------------------------------------------------

	define("SETTINGS_SGBD_HOST", "localhost");
	define("SETTINGS_SGBD_PORT", "3306");
	define("SETTINGS_SGBD_USER", "");
	define("SETTINGS_SGBD_PASSWORD", "");
	define("SETTINGS_SGBD_DATABASE", "");
	
	define("SETTINGS_SGBD_FIX_TABLE", "");
	define("SETTINGS_SGBD_FIX_FIELD", "");

	// ----------------------------------------------------------------------
	// Conectar ao Banco de Dados
	// ----------------------------------------------------------------------
	
	$lobj_databaseObject = mysql_connect(SETTINGS_SGBD_HOST.":".SETTINGS_SGBD_PORT, SETTINGS_SGBD_USER, SETTINGS_SGBD_PASSWORD);
	mysql_select_db(SETTINGS_SGBD_DATABASE);
	if ( $lobj_databaseObject == false ) exit;
	mysql_set_charset('latin1', $lobj_databaseObject); 

	// ----------------------------------------------------------------------
	// Atualiza a tabela corrigindo a codificação
	// ----------------------------------------------------------------------
	
	$lstr_SQLQuery = "SELECT ".SETTINGS_SGBD_FIX_FIELD." FROM ".SETTINGS_SGBD_FIX_TABLE;
	
	$lobj_recordsetFields = mysql_query ($lstr_SQLQuery, $lobj_databaseObject);
	
	if ( mysql_num_rows($lobj_recordsetFields) > 0 ) {
		while ($lobj_rowTerm = mysql_fetch_object( $lobj_recordsetFields )) {

			$lstr_fixFieldContent = eval( "return $lobj_rowTerm->".SETTINGS_SGBD_FIX_FIELD.";");
			$lstr_SQLQuery = "UPDATE ".SETTINGS_SGBD_FIX_TABLE." SET ".SETTINGS_SGBD_FIX_FIELD." = '".mb_convert_encoding($lstr_fixFieldContent, 'ISO-8859-1', 'UTF-8')."' WHERE ".SETTINGS_SGBD_FIX_FIELD." LIKE '$lstr_fixFieldContent'";
			mysql_query ($lstr_SQLQuery, $lobj_databaseObject);
			
		}
	}
	
	// ----------------------------------------------------------------------
	// Desconectar do Banco de Dados
	// ----------------------------------------------------------------------
	
	mysql_close ( $lobj_databaseObject );

?>

Para utilizar o script acima, modifique as constantes:

  • SETTINGS_SGBD_HOST: IP do servidor;
  • SETTINGS_SGBD_PORT: porta de conexão do servidor;
  • SETTINGS_SGBD_USER: nome do usuário;
  • SETTINGS_SGBD_PASSWORD: senha do usuário;
  • SETTINGS_SGBD_DATABASE: banco de dados onde se localiza o problema;
  • SETTINGS_SGBD_FIX_TABLE: tabela onde se localiza o problema;
  • SETTINGS_SGBD_FIX_FIELD: campo onde se localiza o problema.
  • Lembre-se: Este script deve ser executado apenas uma vez! Na segunda execução o script tentará converter novamente os textos para Latin1, removendo todas as letras que estão acentuadas.

    O que você achou? Tem alguma sugestão para resolver este problema ou algo a dizer que complemente este artigo. Deixe seu comentário!

    Até mais!

    Natural de Salvador-BA, é graduado em Analise de Sistemas pela Universidade Católica do Salvador (UCSal, 2003), e Especialista em Engenharia de Software pela Universidade Salvador (2010).

    MySQL: Criando e Restaurando Backup via linha de comando

    Criando Backup:

    Digite: mysqldump -uUSUARIO -pSENHA BANCODEDADOS | gzip > NOMEDOARQUIVODEBACKUP.gz

    Este comando irá criar um backup do banco em um arquivo compactado com o formato GZip

    Restaurando o Backup:

    Digite: gunzip NOMEDOARQUIVODEBACKUP.gz
    Digite: mysql -uUSUARIO -pSENHA BANCODEDADOS < NOMEDOARQUIVO.sql

    Estes dois comandos irão descompactar o arquivo GZip, e restaurá-lo.

    Natural de Salvador-BA, é graduado em Analise de Sistemas pela Universidade Católica do Salvador (UCSal, 2003), e Especialista em Engenharia de Software pela Universidade Salvador (2010).