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
    

Comments

Popular posts from this blog

Reduce TIME_WAIT Socket Connections