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 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.