DZone Snippets: mysql code

mysql importing without dropping tables

mobidata-group (Dallas) — Fri, 10 Oct 2008 07:28:16 GMT

// Had to import a lot of .sql backups into one server. Unfortunately they included a lot of DROP TABLE statements which constantly hosed my data. Here I have skewered sed to delete those sql statements before importing.





# Create a temporary filename

uniq="/tmp/temp_"`date "+%s"`



for item in `ssh user@dbserver ls`;

do

     echo -n "Importing $item..."

     scp user@dbserver\:$item $uniq;

     gunzip < $uniq | sed {/$'DROP TABLE IF EXISTS'/d} | mysql -f -u root $DB_SCHEMA_NAME

     echo "..done"

done



# clean up

rm -f "$uniq"

traffic meter by xorkrus :)

xorkrus (Valentin) — Tue, 07 Oct 2008 23:39:36 GMT

// harsh but worker



#!/usr/bin/perl

use DBD::mysql;

use DBI;

$nowdate = `date +'%Y%m%d'`;chop($nowdate);

$host = ('localhost');

$dbase = ('tmbx');

$user = ('tmbx');

$pass = ('tmbx');

$tzinb = `cat /proc/net/dev | grep ppp0 | awk '{printf \$2}'`;

$tzoutb = `cat /proc/net/dev | grep ppp0 | awk '{printf \$10}'`;

$trc = DBI->trace(0);

        my $dbh = DBI->connect("DBI:mysql:$dbase;$host", "$user", "$pass",{'RaiseError' => 1});

        $lsn = $dbh->prepare("SELECT MAX(sn) FROM session") or die print "$DBI::errstr\n";

        $lsn->execute();

        my $ref = $lsn->fetchrow_hashref();

        $mlsn = $ref->{'MAX(sn)'};

        $lsn->finish();

        my $oth = $dbh->prepare("SELECT * FROM session WHERE sn='$mlsn'") or die print "$DBI::errstr\n";

        $oth->execute();

        my $refa = $oth->fetchrow_hashref();

        $tinb = $refa->{'inb'};

        $toutb = $refa->{'outb'};

        $tdate = $refa->{'date'};

        $oth->finish();

        $nsn = $mlsn+1;

        $tndate = `echo $tdate | sed -e 's/-//'|sed -e 's/-//'`;chop($tndate);

if ($tzinb > $tinb && $tndate < $nowdate) {

        $nzinb = $tzinb-$tinb;

        $nzoutb = $tzoutb-$toutb;

        my $sth = $dbh->prepare("INSERT INTO session (date, sn, inb, outb) VALUES ('$nowdate', '$nsn', '$nzinb', '$nzoutb')") or die print "$DBI::errstr\n";

        &DayRef;

        $sth->execute();

        }

if ($tzinb > $tinb && $tndate == $nowdate) {

        my $sth = $dbh->prepare("UPDATE session SET date='$nowdate', sn='$mlsn', inb='$tzinb', outb='$tzoutb' WHERE sn='$mlsn';") or die print "$DBI::errstr\n";

        $sth->execute();

        }

if ($tzinb < $tinb) {

        my $sth = $dbh->prepare("INSERT INTO session (date, sn, inb, outb) VALUES ('$nowdate', '$nsn', '$tzinb', '$tzoutb');") or die print "$DBI::errstr\n";

        $sth->execute();

        }

sub DayRef {

        $ddate=$nowdate-1;

        my $ath = $dbh->prepare("SELECT SUM(inb), SUM(outb) FROM session WHERE date='$ddate'") or die print "$DBI::errstr\n";

        $ath->execute();

        my $aref = $ath->fetchrow_hashref();

        $mtinb = $aref->{'SUM(inb)'};

        $mtoutb = $aref->{'SUM(outb)'};

        $ath->finish();

        my $ath2 = $dbh->prepare("INSERT INTO traffic (date,inb,outb) VALUES ('$ddate','$mtinb','$mtoutb')") or die print "$DBI::errstr\n";

        $ath2->execute();

        $ath2->finish();}

        $dbh->disconnect();

Rails Migration to convert latin1 MySQL DB to UTF8

regabi (Remi) — Sat, 13 Sep 2008 03:00:38 GMT



class ConvertDbToUtf8 < ActiveRecord::Migration



  def self.up

    db_config = ActiveRecord::Base.connection.instance_values["config"]

    db_name = db_config[:database]

    db_user = db_config[:username]

    db_pass = db_config[:password] || ''

    

    latin1_dump = 'latin1_dump.sql'

    utf8_dump   = 'utf8_dump.sql'

    



    print "Dumping database... "

    system "mysqldump --user=#{db_user} --password='#{db_pass}' --add-drop-table --default-character-set=latin1 --insert-ignore --skip-set-charset #{db_name} > #{latin1_dump}"

    puts "done"

    

    print "Converting dump to UTF8... "    

    system "iconv -f ISO-8859-1 -t UTF-8 #{latin1_dump} | sed 's/latin1/utf8/' > #{utf8_dump}"

    puts "done"

    

    print "Recreating database..."

    system "mysql --user=#{db_user} --password='#{db_pass}' --execute=\"DROP DATABASE #{db_name};\""

    system "mysql --user=#{db_user} --password='#{db_pass}' --execute=\"CREATE DATABASE #{db_name} CHARACTER SET utf8 COLLATE utf8_unicode_ci;\""

    puts "done"

    

    print "Importing UTF8 dump..."

    system "mysql --user=#{db_user} --password='#{db_pass}' --default-character-set=utf8 #{db_name} < #{utf8_dump}"

    puts "done"



    puts " *** don't forget to delete temp files #{latin1_dump} and #{utf8_dump}"

  end



  def self.down

    raise "cant revert sorry"

  end



end

mysql search + replace

wdm (wdm) — Tue, 09 Sep 2008 15:20:18 GMT

// Performing a search-and-replace through a table is easy with MySQL when you know how.
// http://blog.urbanmainframe.com/2008/07/mysql-search-replace/

update table_name set table_field = replace(table_field,'replace_that','with_this');

So, for example, let’s say you have a table called “posts” with a data stored in a field called “content” and you want to replace all instances of the word “dog” with “cat”, then your SQL would look like this:

update posts set content = replace(content,'dog','cat');

MYSQL - column copy with string replacement

srgama (sr) — Thu, 21 Aug 2008 01:46:03 GMT

MySql snippet to copy a column in a table to another column, with some string manipulation.
Create new_col before executing this segment.



UPDATE table SET table.new_col=REPLACE(table.old_col,'str2replace','replace_with_str');

running total in PHP

shantanuo (shantanu oak) — Fri, 08 Aug 2008 11:26:51 GMT

// output running total
// userID,posts,runningTotal|
// 2,23434,28330|
// 6,3443,4896|
// 1,422,1453|
// 3,344,1031|
// 4,344,687|
// 5,343,343|



$q = mysql_query("select * from `members` order by `posts` DESC");

echo 'userID,posts,runningTotal|
';

while($a = mysql_fetch_row($q)){

echo "$a[0],$a[1],$total|
";

$total = $total - $a[1];

}

Create MySQL user

Thu, 31 Jul 2008 16:50:34 GMT

grant all privileges on db_name.table_name (or .*) to 'username'@'localhost' identified by 'password';

Compare Engines

shantanuo (shantanu oak) — Tue, 22 Jul 2008 05:47:46 GMT

// If you have 2 servers with identical database structure, and some of the tables have different engine type, then create a federated table to connect to the original server and compare the engines type with the current table's engine.



CREATE TABLE test.`TABLES2` (

`TABLE_CATALOG` varchar(512) default NULL,

`TABLE_SCHEMA` varchar(64) NOT NULL default '',

`TABLE_NAME` varchar(64) NOT NULL default '',

`TABLE_TYPE` varchar(64) NOT NULL default '',

`ENGINE` varchar(64) default NULL,

`VERSION` bigint(21) default NULL,

`ROW_FORMAT` varchar(10) default NULL,

`TABLE_ROWS` bigint(21) default NULL,

`AVG_ROW_LENGTH` bigint(21) default NULL,

`DATA_LENGTH` bigint(21) default NULL,

`MAX_DATA_LENGTH` bigint(21) default NULL,

`INDEX_LENGTH` bigint(21) default NULL,

`DATA_FREE` bigint(21) default NULL,

`AUTO_INCREMENT` bigint(21) default NULL,

`CREATE_TIME` datetime default NULL,

`UPDATE_TIME` datetime default NULL,

`CHECK_TIME` datetime default NULL,

`TABLE_COLLATION` varchar(64) default NULL,

`CHECKSUM` bigint(21) default NULL,

`CREATE_OPTIONS` varchar(255) default NULL,

`TABLE_COMMENT` varchar(80) NOT NULL default ''

)

ENGINE=FEDERATED DEFAULT CHARSET=latin1

CONNECTION='mysql://root@172.172.172.172/information_schema/TABLES';



SELECT b.TABLE_SCHEMA as remote_database, b.TABLE_NAME as remote_tableName, b.ENGINE as remote_engine, a.ENGINE AS local_engine 

FROM test.TABLES2 AS a INNER JOIN information_schema.TABLES as b 

ON a.TABLE_SCHEMA = b.TABLE_SCHEMA AND a.TABLE_NAME = b.TABLE_NAME AND a.ENGINE != b.ENGINE;

Claves Foraneas / Foreign key

Ricardo (Ricardo m. García) — Wed, 16 Jul 2008 12:18:14 GMT

Integridad de datos mediantes claves Foraneas, script que genera las SQL necesarios para garantizar la integridad entre tablas. Poco a poco vamos relacionando parejas de padre e hijo y podemos realizar un arbol de claves foraneas, cuando si borramos un registro del padre, borra y actualiza a los hijos en cascada, si no me explico bien decirme en los comentarios.



require_once("cabezera/funciones.php");

conectar_mysql();

require_once("cabezera/scripts/constantes.php");

?>









Untitled Document







$ejecutar = isset($_GET['ejecutar']) && $_GET['ejecutar']=='si';

$tabla_padre = "album";

$tabla_hijo = "fotos";

$campo_padre = "idAlbum";

$campo_hijo = "idAlbum";



$sinIntegridad = 0;



$sql="select * from ".$tabla_hijo;

$resultado = mysql_query($sql);

while($fila = mysql_fetch_array($resultado))

{

	$sql2 = "select * from ".$tabla_padre." where ".$campo_padre." = '".$fila[$campo_hijo]."'";

	//echo $sql2."
";

	$resultado2 = mysql_query($sql2);

	if(mysql_num_rows($resultado2) <= 0)

	{

		$sql3 = "DELETE FROM `".$tabla_hijo."` WHERE `".$campo_hijo."` = '".$fila[$campo_hijo]."' LIMIT 1;";

		if($ejecutar)

		{

			if(mysql_query($sql3)) echo "OK query : ".$sql3."
";

			else echo "Error en query : ".$sql3."
";

		}

		else

		{

			echo $sql3."
";

		}

		$sinIntegridad++;

	}

}



$sql1="ALTER TABLE `".$tabla_padre."` ADD INDEX ( `".$campo_padre."` ) ";

$sql2="ALTER TABLE `".$tabla_hijo."` ADD INDEX ( `".$campo_hijo."` ) ";

$sql3="ALTER TABLE ".$tabla_hijo." ADD FOREIGN KEY(".$campo_hijo.") REFERENCES ".$tabla_padre." (".$campo_padre.") ON DELETE CASCADE ON UPDATE CASCADE";

if($ejecutar)

{

	if(mysql_query($sql1)) echo "OK query : ".$sql1."
";

	else echo "Error en query : ".$sql1."
";

	

	if(mysql_query($sql2)) echo "OK query : ".$sql2."
";

	else echo "Error en query : ".$sql2."
";

	

	if(mysql_query($sql3)) echo "OK query : ".$sql3."
";

	else echo "Error en query : ".$sql3."
";

}

else

{

	echo $sql1."
";

	echo $sql2."
";

	echo $sql3."
";

}



echo "
".$sinIntegridad." filas sin integridad.
";

?>



  

  

  

  

  



/*

ALTER TABLE fotos_nueva_version ADD FOREIGN KEY(idUsuario) REFERENCES usuarios_portalcocinas (idUsuario) ON DELETE CASCADE ON UPDATE CASCADE



ALTER TABLE relacion_usuario_localidad ADD FOREIGN KEY(idUsuario) REFERENCES usuarios_portalcocinas (idUsuario) ON DELETE CASCADE ON UPDATE CASCADE





ALTER TABLE relacion_usuario_actividad ADD FOREIGN KEY(idUsuario) REFERENCES usuarios_portalcocinas (idUsuario) ON DELETE CASCADE ON UPDATE CASCADE





ALTER TABLE fotos_nueva_version DROP FOREIGN KEY idUsuario_FK;



SHOW CREATE TABLE fotos_nueva_version;

*/

?>

Query MySQL accent insensitive in latin1_general_ci

ki4ngel (Benoit Asselin) — Sat, 21 Jun 2008 10:34:59 GMT

Query MySQL accent insensitive in latin1_general_ci



SELECT * FROM `table`WHERE `text` LIKE CONVERT(_utf8 '%cinema%' USING utf8) COLLATE utf8_general_ci ;

Source: Query MySQL accent insensitive in latin1_general_ci