Snippet MySQL
With snippet MySQL, you can free your mind
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
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;
Clone Tables in MySQL
CREATE TABLE clone_tbl LIKE tbl; INSERT INTO clone_tbl SELECT * FROM tbl;
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;
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);
Replace String of Field
UPDATE t1 SET col1 = REPLACE(REPLACE(col1, '\r', ' '), '\n', ' ');
Remove String of Field
UPDATE t1 SET col1 = TRIM(TRAILING '\n' FROM col1);
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
mysqldump
with conditionmysqldump
has the--where
option to execute aWHERE
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.mysql
export data to csv or query to csvmysql -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
Post a Comment