A unique article we have came across containing many things to study from. You will probably want to give it a look to see if you agree.
MySQL has a lot of usefull features that are not known by many programmers and DBAs. This article gives a short list of such features.
SQL_CALC_FOUND_ROWS
When it is necessary to extract data limited by certain number of records at one time and get total number of records meeting the condition, keep in mind the following elegant solution:
SELECT SQL_CALC_FOUND_ROWS * FROM `table` WHERE id > 100 LIMIT 10;
SELECT FOUND_ROWS();
First query return 10 records meeting the condition. Second query returns number of records that should return previous query composed without LIMIT.
INSERT IGNORE
When you insert new record into a table having primary or unique key and want to avoid duplicate key error, take a look on INSERT IGNORE statement. Usually you can resolve duplicate key conflicts on PHP as follows:
?// find a row
?$ row = query(‘SELECT * FROM table WHERE id=1’);
?// if there is no row, insert new one
?if (!$ row) {
?query(‘INSERT INTO table ?’);
?}
But we can do the same with just 1 MySQL query without PHP:
INSERT IGNORE INTO table ?
ON DUPLICATE KEY UPDATE
There is common task -?if the object exist update it, otherwise create new one. This is how we can do it on PHP:
?// look for record by certain condition
?$ row = query(‘SELECT * FROM `table` WHERE id=1’);
?// if record exists
?if ($ row) {
?// update it
?query(‘UPDATE `table` SET value=value+1 WHERE id=1’);
?}
?else {
?// insert new record
?query(‘INSERT INTO `table` SET value=1, id=1’);
?}
We can do the same with just 1 MySQL query without PHP assuming table has primary or unique key on ID:
INSERT INTO `table` SET value=1, id=1 ON DUPLICATE KEY UPDATE value=value+1
ALTER IGNORE TABLE?
Assume there is table with the following structure and data:
TestTab (f1 int, f2 int, f3 varchar(20))
? ? f1 ? f2 ? f3 ?
—————————
? 1 2 row?1
? 1 2 row 2
? 1 3 row 3
? 2 3 row 4
? 3 4 row 5?
Now it is required to remove duplicate entries in first two columns from the table.
We can do this by running the following MySQL query:
ALTER IGNORE TABLE `TestTab` ADD UNIQUE INDEX(f1,f2);
More articles about MySQL and other databases can be found at
http://www.convert-in.com/articles.htm
Intelligent Converters, one of the world’s leading software vendors in data conversion field, entered the software market in 2001. The main goal of our software is to convert data with a minimum loss of information (e.g. formatting properties for text documents or field attributes for databases). Intelligent Converters products are well-known all around the world. There are converters between the most popular databases: Oracle, MySQL, Microsoft Access, Microsoft SQL, IBM DB2 and export tools for Adobe PDF to convert data into Microsoft Word, HTML, plain text, Microsoft Excel and XML. All of them combine ease of use with sufficient capacity to guarantee the best quality of the conversion process.
Article source: http://table.ezinemark.com/unexplored-mysql-18e39bf261c.html.
I thought that was interesting. Feel free to leave your comments below.