Category Archives: Databases

Difference between MySql drop table, truncate table, delete from table commands

DROP TABLE tablename;

DROP command is used for deleting the table and its structure from the data base.Use this command when you don’t need that table any more.

 

Other Spec:

1. Removes a table from the database.Table structures, indexes, privileges, constraints will also be removed.
2. Cannot be rolled back.
3. No Triggers can be fired.

 

DELETE FROM tablename;

DELETE command is used for deleting the records from the table, and it removing the table space which is allocated by the database, and returns number of rows deleted.Delete allows you to use a WHERE clause so you can delete specific records according to your requirement.

 

Other Spec:

1. Can be rolled back, as during DELETE operation, all the data get copied into Rollback Table space first, and then delete operation perform.
2. Does not reset identity of the table.
3. Triggers can be fired.

 

TRUNCATE TABLE tablename;

TRUNCATE operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.
After this, the table is empty, and auto-incrementing keys are reset to 1.It faster than DELETE because delete scan the table to generate a count of rows that were affected.

 

Other Spec:

1. Removes all rows from a table, but the table structures and its columns, constraints, indexes remains.
2. Cannot be rolled back.
3. Resets the identity of the table.
4. Cannot use TRUNCATE on a table referenced by a FOREIGN KEY constraint.
5. No Triggers can be fired.
6. Cannot use WHERE conditions.

 

ACID (Atomicity Consistency Isolation Durability) Model of database

ACID(Atomicity Consistency Isolation Durability) is a set of properties that guarantee that database transactions are processed reliably.

In the context of databases, a single logical operation on the data is called a transaction.

 

Atomicity
Atomicity is an all-or-none proposition.
Modifications on the data in the database either fail or succeed. Each transaction is said to be “atomic.” If one part of the transaction fails, the entire transaction fails. It is critical that the database management system maintain the atomic nature of transactions in spite of any DBMS, operating system or hardware failure.

 

Consistency
Consistency guarantees that a transaction never leaves your database in a half-finished state.
Only valid data (valid according to integrity constraints) may be committed. If, for some reason, a transaction is executed that violates the database’s consistency rules, the entire transaction will be rolled back and the database will be restored to a state consistent with those rules. On the other hand, if a transaction successfully executes, it will take the database from one state that is consistent with the rules to another state that is also consistent with the rules.

 

Isolation
Isolation keeps transactions separated from each other until they’re finished.
One transaction does not interfere with another. The ‘executor’ of a transaction has the feeling that he has the entire database for himself.

 

Durability
A committed transaction will not be lost.
Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors,  i.e. the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.

 

Example of printer to understand the ACID property more clearly.
Atomicity
Either the entire document gets printed or nothing at all.

Consistency
At end-of-transaction, the paper feed is positioned at top-of-page.

Isolation
No two documents get mixed up while printing.

Durability
The printer can guarantee that it was not “printing” with empty cartridges

 

ACID properties provide the basic building blocks of any database transaction model. So spend few minutes to review these characteristics and commit these in your memory 🙂

 

How to use multiple MYSQL databases in a project

If your databases are located on same MySQL server.You don’t need two connections, you can access them both as unique user.

You also don’t need to select a DB. Just use the database-name as prefix when specifying the tables. That’s it.

[php]
<?php

mysql_connect("localhost","username","password") or die(mysql_error());

$query = "SELECT * FROM database1.tablename";

$result = mysql_query($query)or die(mysql_error());

var_dump($result);

$query2 = "SELECT * FROM database2.tablename";

$result2 = mysql_query($query2)or die(mysql_error());

var_dump($result2);

?>
[/php]

Or if you want to select database, you can do

Please note there can only be one active DB at a time, it will work in this way:

[php]
<?php

$conn1 = mysql_connect("localhost","username","password") or die(mysql_error());

$conn2 = mysql_connect("localhost","username","password",true) or die(mysql_error());

mysql_select_db("database1",$conn1);

// tablename is a table of database1

$query = "SELECT * FROM tablename";

$result = mysql_query($query,$conn1);

var_dump($result);

mysql_select_db("database2",$conn2);

// tablename is a table of database2

$query2 = "SELECT * FROM tablename";

$result2 = mysql_query($query2,$conn2);

var_dump($result2);

?>
[/php]

Although there’s no need for 2 connections; you can select both DB’s using the same connection.

You can make multiple calls to mysql_connect(), but if the parameters are the same you need to pass true for the ‘$new_link’ (fourth) parameter, otherwise the same connection is reused.

So then you have to do is

[php]
<?php

$db_conn1 = mysql_connect($hostname, $username, $password);

$db_conn2 = mysql_connect($hostname, $username, $password, true);

mysql_select_db(‘database1’, $db_conn1);

mysql_select_db(‘database2’, $db_conn2);

//Then to query database 1, do

mysql_query(‘select * from tablename’, $db_conn1);

//and for database 2

mysql_query(‘select * from tablename’, $db_conn2);

?>
[/php]

It is used if MySQL server of database is different.

 

A simple PHP script for MySQL Database backup

PHP: Backup Your Website’s MySQL Database

 

It is always good to backup your MySQL database regularly. You should also create a backup before making any major changes, in case something goes wrong and you need to revert to the unmodified version, then you will found yourself in safe hand. Database backups can also be used to move your database from one server to another if you change hosting company.

 

If you want to take backup of your complete database using the command prompt then follow the below command.

 

[sourcecode language=”plain”]
mysqldump -u yourusername -p yourpassword yourdatabasename > yourbackupfilename.sql
[/sourcecode]

 

Please replace yourusername, yourpassword, yourdatabasename, yourbackupfilename according to your real credentials.

 

You can also do the same job using ‘php script for mysql database backup’.
Follow the below script to have backup of your complete database or some tables.

 

You just need to provide database credentials to the function, as well as an array of tables you’d like to backup leave it if you want to take complete database backup.

 

[php]
<?php
backup_tables(‘HOSTNAME’,’USERNAME’,’PASSWORD’,’DATABASE’);

/* Function to create backup of a database */
function backup_tables($host,$user,$pass,$name,$tables = ‘*’)
{
$link = mysql_connect($host,$user,$pass);
mysql_select_db($name,$link);

if($tables == ‘*’){
$tables = array();
$result = mysql_query(‘SHOW TABLES’);
while($row = mysql_fetch_row($result)){
$tables[] = $row[0];
}
}else{
$tables = is_array($tables) ? $tables : explode(‘,’,$tables);
}

//Looping of tables
foreach($tables as $table){
$result = mysql_query(‘SELECT * FROM ‘.$table);
$num_fields = mysql_num_fields($result);

$return.= ‘DROP TABLE IF EXISTS ‘.$table.’;’;
$row2 = mysql_fetch_row(mysql_query(‘SHOW CREATE TABLE ‘.$table));
$return.= "\n\n".$row2[1].";\n\n";

for ($i = 0; $i < $num_fields; $i++)
{
while($row = mysql_fetch_row($result))
{
$return.= ‘INSERT INTO ‘.$table.’ VALUES(‘;
for($j=0; $j<$num_fields; $j++)
{
$row[$j] = addslashes($row[$j]);
$row[$j] = preg_replace("/\r\n/","\\r\\n",$row[$j]);
if (isset($row[$j])) { $return.= ‘"’.$row[$j].’"’ ; } else { $return.= ‘""’; }
if ($j<($num_fields-1)) { $return.= ‘,’; }
}
$return.= ");\n";
}
}
$return.="\n\n\n";
}

//Save .sql file
$handle = fopen($name.’-backup-‘.date(‘d-m-y-H-i-s’).’.sql’,’w+’);
fwrite($handle,$return);
fclose($handle);

/*
@ Use this code if you want to create zipped file
*/
/*
$gzdata = gzencode($return, 9);
$handle = fopen($name.’-backup-‘.date(‘d-m-y-H-i-s’).’.sql.gz’,’w+’);
fwrite($handle, $gzdata);
fclose($handle);
*/
}
?>
[/php]

 

We all know how important the database backup is, so don’t hesitate or waste your time in thinking else, have backup your database at a regular interval

Have backup, keep yourself in safe side, Have fun, Enjoy Scriptarticle!!

 

PHP: Convert MySQL data latin1 to UTF8 character set

Turning or convert MySQL data latin1 to utf8

 

The default character set for MySQL is latin1 (As of PHP 5.6.0, default_charset value is used as the default. From PHP 5.4.0, UTF-8 was the default, but prior to PHP 5.4.0, ISO-8859-1 was used as the default. It’s therefore a good idea to always explicitly specify UTF-8 to be safe, even though this argument is technically optional).

 

This will not support Chinese, Hindi, French and other weird multi byte character sets languages.
It will quietly support them, but returns rubbish and will cause only frustration 🙁

 

The best character set to use Hindi and other languages is UTF-8.

Convert your database character-set from latin1 to utf8 by using the below code.

 

[php]

<?php

mysql_connect(‘HOSTNAME’, ‘DB_USER’, ‘DB_PASSWORD’);
mysql_select_db(‘DATABASE_NAME’);

// Set UTF-8 as the default character set for all MySQL database tables
mysql_set_charset(‘utf8’, $link);

$res = mysql_query("SHOW TABLES");
while($row = mysql_fetch_array($res))
{
$tablename = $row[‘Tables_in_’.DATABASE_NAME];
echo $query = "ALTER TABLE $tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci";
echo ‘<br>’;
mysql_query($query);
}

?>

[/php]

 

 

I think the above article will help you to convert the encoding for your database tables and save your lot of affords as you have expected to converting these one by one 🙂

 

Post your comments with suggestion (if you have will be always valuable), if the above one not works for you or you have any more related suggestions.

 

 

Delete all duplicate rows except for one in MySQL

Delete all duplicate rows except for one in MySql having no primary key.

Someone asked me to delete all rows from a table except one entry with a single query.
This is not so easy for me :), Anyway after work and research I have found the solution, I am sharing with you How you can do the same.

Let’s take an example of table tbl_employee have duplicate names data as follows

 

SELECT * FROM tbl_employee;
——————–
id    name
——————–
1     mahesh
2     suresh
3     john
4     john
5     john
6     mahesh
——————–

 

If you want to keep the row with the lowest id value then

DELETE e1 FROM tbl_employee e1, tbl_employee e2 WHERE e1.id > e2.id AND e1.name = e2.name

Or with highest id value

DELETE e1 FROM tbl_employee e1, tbl_employee e2 WHERE e1.id < e2.id AND e1.name = e2.name