table | Z-Car

MySQL – A simple script to truncate all MySQL tables in a Database

By Prodromus December 22, 2010 MySQL, Scripting No Comments

Want a quick and dirty way to Truncate all the tables in a MySQL DB? You can use the following one line script.

mysqldump -ppassword YourDBName –no-data dumpfile | mysql dumpfile

What this does is uses mysqldump to output all the tables in the database “YourDBName” into a dump file. This dump file contains drop statements, and create table statements for each table in the database. What this effectively does is drops every table, and recreates it WITHOUT any data inside. Be very careful, since this is a very powerful script, it has the ability to completely wipe out your database with no hope of recovery unless you have a current backup.

For additional ways to execute specific commands against all tables in a DB, look into using mk-find in Maatkit, it is an excellent tool for advanced MySQL scripting.

MySQL – Select rows from a table when a field starts with a number

By Prodromus December 22, 2010 MySQL, Query Optimization 1 Comment

A common request is how to select records from a table when a specific field starts with a number. This request often is used when searching free-form text fields where the fields were used to provide an answer field to a question.

The simplest way to attack this is by using a regular expression. For example:

SELECT * FROM BadlyDesignedTable WHERE AnswerColumn regexp ‘^[0-9]+’;

or
SELECT * FROM BadlyDesignedTable WHERE AnswerColumn RLIKE ‘^[0-9]+’;

Regex and RLIKE are compatible keywords that both represent regular expression matching.

The regex is described as follows :

^ – Start anchor, used to ensure the pattern matches start of the string.
[ – Start of character class.
0-9 – Any digit
] – End of character class

Exiting a MySQL Stored Procedure in the middle of the code

By Prodromus December 17, 2010 MySQL No Comments

While exiting a MySQL function is fairly straight-forward, simply use the RETURN keyword, exiting a Stored Procedure is not quite as obvious. There is no EXIT keyword, however you can use the LEAVE keyword and specify a label that is associated with the BEGIN of the Stored Procedure. In this way, you are defining the Stored Procedure with a Label, which the LEAVE statement can then act upon.

As an example, look at the following Stored Procedure :

CREATE PROCEDURE TestProc(Value INT)

ThisSP:BEGIN

IF Value is null or Value=0 then
Select ‘Invalid Value’;
LEAVE ThisSP;
END IF;

Select * from Table twhere t.Value=Value;

END;

MySQL – Best way to remove all data from a table

By Prodromus November 22, 2010 MySQL, Scripting No Comments

There are multiple ways in which to remove all data from a MySQL Table.

The first is to use the DROP TABLE command, which will remove the entire table with data. This basically removes the entire table, you will not be able to recover the structure or data.

The second is DELETE * FROM Table which will remove the data, leaving the auto-increment values alone, as well as the rest of the table structure. This method is very slow on large tables using Innodb. This is because each record has to be written to the log. In general, you should never use this method to remove all data from a table.

The third method is to issue a TRUNCATE TABLE command which will quickly remove all the data from the table, reset the auto-increment values (but leave them as auto-increment columns, so they will just start at 1 and go up from there again).

Lastly, you can use the nuclear option, which is as follows :

mysqldump -ppassword YourDBName YourTblName –no-data dumpfile | mysql dumpfile

This will drop the table, and then recreate it all from scratch.

MySQL Delimiters – Or why I hate stored procedures and Error Code : 1064 You have an error in your SQL syntax.

By Prodromus August 19, 2010 MySQL, Stored Procedure 1 Comment

MySQL 5.0+ gave DBA’s the ability to write stored procedures and functions. This is a great addition, and one that all “real” DB engines should provide. But, as many DBA have found out, writing stored procedures using most MySQL clients can be a bit of a challenge. Usually after writing their first attempt, they are greeted with, “Error Code : 1064 You have have an error in your SQL syntax;”.

So, what is going on? Well, by default the MySQL statement delimiter is the semi-colon (;). However, the mysql command-line utility also uses a semi-colon as a delimiter. So, if the command-line utility were to interpret the ; characters inside of the stored procedure itself, those would not end up becoming part of the stored procedure, and that would make the SQL in the stored procedure syntactically invalid.

The solution is to temporarily change the command-line utility delimiter using the DELIMITER command, as seen here:

DELIMITER //

CREATE PROCEDURE sku_pricing( OUT low_price DECIMAL(8,2), OUT high_price DECIMAL(8,2), OUT avg_price DECIMAL(8,2) )

BEGIN

SELECT Min(prod_price) INTO low_price FROM sku_items;

SELECT Max(prod_price) INTO high_price FROM items;

SELECT Avg(prod_price) INTO avg_price FROM items;

END; //

DELIMITER ;

Here, DELIMITER // tells the command-line utility to use // as the new end of statement delimiter, and you will notice that the END that closes the stored procedure is defined as END // instead of the expected END;. This way the ; within the stored procedure body remains intact and is correctly passed to the database engine, rather than being interpreted by the client. And then, to restore things back to how they were initially, the statement closes with a DELIMITER ;. Any character may be used as the delimiter except for , as it is an escape character in mySQL.

How to increase MySQL performance when loading a Dump File

By Prodromus August 19, 2010 MySQL, Restore Backup No Comments

In MYSQL, as well as most database engines, restoring a backup, or importing from an existing dump file can take a long time depending on the number of indexes and primary keys you have on each table. You can speed this process up dramatically by modifying your original dump file by surrounding it with the following:

SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;

.. your dump file ..

SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;

This will force MySQL to not commit until all rows have been loaded, as well as skip all foreign keys checks. Only skip these checks if you are 100% sure that no constraint is violated. This will usually be the case when dumping from one table and inserting into another.

« Previous