some MySQL recipes

MySQL recipes

Using User Variables

mysql> SET @myvar := 13;

mysql> SET @t1=1, @t2=2, @t3:=4;
mysql> SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3;

Trick to build identifiers from User Variables

mysql> SET @c = "c1";
mysql> SET @s = CONCAT("SELECT ", @c, " FROM t");
mysql> PREPARE stmt FROM @s;
mysql> EXECUTE stmt;
+----+
| c1 |
+----+
|  0 |
+----+
|  1 |
+----+
mysql> DEALLOCATE PREPARE stmt;

Get the actual datetime to modify the stop_date

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2008-07-28 12:23:54 |
+---------------------+
1 row in set (0.00 sec)

Casting any time type to DATETIME

mysql> SET @refdate := CAST(NOW() AS DATETIME);
mysql> SELECT @refdate;
| 2012-09-15 00:00:00 |
select * from att_00025 where time>@refdate order by time limit 1;

CASE SENSITIVE QUERY

INSENSITIVE: select * from tasks where name like "%MAC%";
SENSITIVE: select * from tasks where name like "%MAC%" collate latin1_bin;

MODIFY THE TYPE OF A TABLE

//change allows to rename, modify only changes type
alter table device change old_column_name new_column_name varchar(255) NOT NULL;
alter table device modify alias varchar(255);

How to Get the Storage Engine of a TABLE

mysql> select TABLE_NAME,ENGINE from information_schema.TABLES where not ENGINE;

Get Database Name from itself

mysql> Select SCHEMA();

Insert between databases

mysql> insert into amt select * from hdb_amt.amt where stop_date is null;

Get Size in Rows for all tables in a database

mysql> select table_name,table_rows from information_schema.tables where table_schema = 'hdb';

Get first/last unordered rows using limit

Example 1: Returning the first 100 rows from a table called employee:

 select * from employee limit 100 

Example 2: Returning a range of rows from a table called employee (starting at record 2, return the next 4 rows):

 select * from employee limit 2,4

Count distinct occurrences of a value grouped by another column

It will return counts of daily registers grouped by month.

SELECT year, month, COUNT(DISTINCT day) AS days FROM t1 GROUP BY year,month;