13 mysql tips that you don’t know

Here is a list of thirteen tips that can be usefull for your queries. If you know more tips send…

October 19, 2012

Here is a list of thirteen tips that can be usefull for your queries. If you know more tips send comments.

1 REMAP VALUES INSIDE A QUERY USING CASE…WHEN SYNTAX

SELECT id,title,
   (CASE date WHEN '0000-00-00' THEN '' ELSE date END) AS date
   FROM your_table

SELECT id,title,
   (CASE status WHEN 0 THEN 'open' WHEN 1 THEN 'close' ELSE 'standby' END) AS status 
   FROM your_table

2 FIND DUPLICATE RECORDS WITH EMAIL FIELD

SELECT email, COUNT(email) AS q 
   FROM emails_table GROUP BY email HAVING q > 1 
   ORDER BY q DESC

3 EXTRACT RECORDS WITH A RANDOM ORDER

SELECT * FROM your_table ORDER BY RAND()

4 REPLACE STRINGS IN A FIELD WITH AN UPDATE

UPDATE your_table 
   SET name=REPLACE(name, 'John', 'Johnny')
   WHERE name LIKE '%John%';

5 RESET THE AUTOINCREMENT COUNTER IN A TABLE

ALTER TABLE your_table AUTO_INCREMENT = 100

Next record you insert will have id=100.

6 ADD AN AUTOMATIC INCREMENT COLUMN FOR A SELECT

set @N = 0;
SELECT @N := @N +1 AS number, name, surname FROM people;

7 JOINING FIELDS WITH CONCAT FUNCTION

SELECT CONCAT(name,' ',surname) AS complete_name FROM users

8 SELECT PARTIAL DATE VALUES WITH DATE FUNCTIONS

SELECT id,title, YEAR(date_field) FROM your_table
SELECT id,title, 
   CONCAT(MONTH(date_field),'/',YEAR(date_field)) as new_date
   FROM your_table

9 INSERTING ROWS IGNORING DUPLICATES ON A FIELD WITH UNIQUE KEY
On a table “tags” with a unique key on ‘tag’ field:

INSERT IGNORE INTO tags (tag) VALUES ('good');

You can run this query many times, no error will be returned.

10 USING FULLTEXT INDEX AND MATCH AGAINST SEARCH

select * from articles where MATCH(content_column) AGAINST ('music')

To work, this tip, need to add the full text index on the content_column. Note that if you already have a table filled with data, adding the index will not create it… so you have to start from an empty table.

11 HOW TO SAY “ONE MONTH AGO” IN MYSQL

SELECT user, count(*) AS logins 
   FROM stat_log 
   WHERE action='LOGIN' AND dt_when >= DATE_ADD(CURDATE(), INTERVAL -1 MONTH)
   GROUP BY user

This where clause with dt_when lets you count the records that has date greater or equal to one month ago.

12 SET CORRECT CHARSET

SET NAMES 'utf8';

Run this query after your connection starts. More info here.

13 INSERTING FROM A TABLE TO ANOTHER

INSERT INTO yourtable (field1,field2,field3) 
   SELECT newfield1,newfield2,'fixed value'
   FROM yourtable2

You can use this construct to copy rows from a table to another and add also some values that you specify in the second part of the query.

Do you know other tips? Write them in the comments.

Author

I’m a software engineer, an everyday web developer and a maker. I usually build sites with PHP, within or without WordPress. I build Internet of Things with Arduino and ESP8266. I’m the founder of Rockit.it and Dailybest.it and I’m actually the Chief Technical Officer of Better Days web agency.


Comments on “13 mysql tips that you don’t know”

2 thoughts

  1. Vivek says:

    For : ALTER TABLE your_table AUTO_INCREMENT = 100
    You cannot reset the counter to a value less than or equal to any that have already been used. May be you would like to add.

  2. Chris says:

    Absolutely superb list! The Insert Select and Replace items with an Update have blown my mind!

Comments are closed

Recommended

How to remove custom fields from WordPress

Ok. You’ve added custom fields in wordpress, but how can you remove them? How to delete custom fields wordpress Two…

September 29, 2013

10 PHP usefull functions for MySQLi stuff (mysql improved)

I’ve modified the previous post to use mysqli instead of mysql. Was it necessary? I think it was good step…

January 29, 2010

10 PHP usefull functions for MySQL stuff

Here is my personal collection of 10 php function that I always include in my database function library. I think…

January 25, 2010

How to add rel=”nofollow” to links with preg_replace()

Adding rel="nofollow" to external link is a good SEO practice.

September 22, 2015

Social buttons: the fastest way for WordPress, without plugins

NOTE: the code in this post is written for WordPress but you can easily translate it in any language. You’re here…

September 15, 2015

Optimize WordPress, a long list of tips

In the above image you can see your WordPress before reading this post, and after the optimizations you will make…