Snippet MySQL

With snippet MySQL, you can free your mind

1. GroupBy_Concat

   DECLARE @Separator AS NVARCHAR = '-';
   SELECT categoryid ,sum(unitprice) as sumprice,
     STUFF((SELECT @Separator + cast(productid as nvarchar)
         FROM products
         WHERE categoryid = a.categoryid and unitprice > 20 and categoryid = 2
         FOR XML PATH ('')
       ) , 1, 1, '')  AS IDList,
     STUFF((SELECT @Separator + cast(productname as nvarchar)
         FROM products
         WHERE categoryid = a.categoryid and unitprice > 20 and categoryid = 2
         FOR XML PATH ('')
       ) , 1, 1, '')  AS NameList
   FROM products as a
   where unitprice > 20 and categoryid = 2
   GROUP BY categoryid
   

2. Partitioning in MySQL

   -- partitioning by range for existing table
   ALTER TABLE tbl
     PARTITION BY RANGE COLUMNS (`DATE`)
     SUBPARTITION BY HASH (COL1) (
       PARTITION p_201412 VALUES LESS THAN ('2015-01-01'),
       PARTITION p_201501 VALUES LESS THAN ('2015-02-01'),
       PARTITION p_201502 VALUES LESS THAN ('2015-03-01'),
       PARTITION p_201503 VALUES LESS THAN ('2015-04-01'),
       PARTITION p_201504 VALUES LESS THAN ('2015-05-01'),
       PARTITION p_201505 VALUES LESS THAN ('2015-06-01'),
       PARTITION p_201506 VALUES LESS THAN ('2015-07-01'),
       PARTITION p_201507 VALUES LESS THAN ('2015-08-01'),
       PARTITION p_201508 VALUES LESS THAN ('2015-09-01'),
       PARTITION p_201509 VALUES LESS THAN ('2015-10-01'),
       PARTITION p_201510 VALUES LESS THAN ('2015-11-01'),
       PARTITION p_201511 VALUES LESS THAN ('2015-12-01'),
       PARTITION p_201512 VALUES LESS THAN MAXVALUE
     );
   -- drop partition without dropping data in MySQL
   ALTER TABLE tbl REMOVE PARTITIONING;
   

3. Clone Tables in MySQL

   CREATE TABLE clone_tbl LIKE tbl;
   INSERT INTO clone_tbl SELECT * FROM tbl;
   

4. Get Size of Databases

   SELECT table_schema AS "Database Name",
     ROUND(SUM(data_length + index_length)/1024/1024,1) AS "Database Size in MB",
     ROUND(SUM(data_free)/1024/1024,1) AS "Free Space in MB"
   FROM information_schema.TABLES
   GROUP BY table_schema;
   

5. Set Default Value for Datetime Column

   CREATE TABLE t1 (ts TIMESTAMP DEFAULT '0000-00-00 00:00:00');
   CREATE TABLE t2 (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
   CREATE TABLE t3 (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
   

6. Replace String of Field

   UPDATE t1 SET col1 = REPLACE(REPLACE(col1, '\r', ' '), '\n', ' ');
   

7. Remove String of Field

   UPDATE t1 SET col1 = TRIM(TRAILING '\n' FROM col1);
   

8. Drop or Truncate Database

   # Drop Database
   mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "drop table $table" DATABASE_NAME; done
   # Truncate Database
   mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "truncate table $table" DATABASE_NAME; done
   

9. mysqldump with condition

   mysqldump has the --where option to execute a WHERE clause for a given table.

   mysqldump -u... -p... --where="date between '2015-01-01' and '2015-12-31'" mydb table3 > table3.sql
   mysqldump -u... -p... --where="name in ('fee','fi','fo','fum')" mydb table3 > table3.sql
   mysqldump -u... -p... --lock-all-tables --where="table3_id in (select id from table3 where name in ('fee','fi','fo','fum'))" mydb table2 > table2.sql
   mysqldump -u... -p... --lock-all-tables --where="id in (select table1_id from table2 where table3_id in (select id from table3 where name in ('fee','fi','fo','fum')))" mydb table1 > table1.sql
   

   Note: Since the second and third mysqldumps require using more than one table, --lock-all-tables must be used.

10. mysql export data to csv or query to csv

    mysql -B -u... -p... mydb -h 127.0.0.1 -e "SELECT * FROM tbl1;" > tbl1.sql
    mysql -B -u... -p... mydb -h 127.0.0.1 -e "SELECT * FROM tbl1;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > tbl1.sql
    # s/'/\'/          replace ' with \'
    # s/\t/\",\"/g     replace all \t (tab) with ","
    # s/^/\"/          at the beginning of the line place a "
    # s/$/\"/          at the end of the line place a "
    # s/\n//g          replace all \n (newline) with nothing