[SQL queries/scripts] Useful SQL queries and scripts

batch_delete_mysql.py

import subprocess
import re
import time

def delete():
    stm = [
        'mysql',
        '-u', 'test',
        '--password=dummypassword',
        '-e', "DELETE FROM mcm.test ORDER BY id LIMIT 1000",
        '--skip-column-names',
        '-vvv'
    ]
    
    result = subprocess.run(stm, stdout=subprocess.PIPE)
    output = result.stdout.decode('utf-8')
    print(output)

    match = re.search( r'(\d*) rows affected', output)
    affected_rows = int(match.group(1))
    return affected_rows

affected_rows = 1000

while affected_rows > 0:
  affected_rows = delete()
  time.sleep(1)

copy_table_full.sql

--------------- MYSQL ---------------

-- Perform copy of table including structure, data, indexes and triggers

CREATE TABLE newtable LIKE oldtable; 
INSERT newtable SELECT * FROM oldtable;

-------------------------------------

empty_string_2_null.sql

--------------- MYSQL ---------------

UPDATE schema1.table1
SET
	col1 = NULLIF(col1,''),
	col2 = NULLIF(col2,'');

-------------------------------------

import_csv.sql

--------------- MYSQL ---------------

-- WARNING: This operation can not be rolled back even when executed in transaction!

LOAD DATA LOCAL INFILE 'path/to/file.csv' 
INTO TABLE schema.table
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n' -- on linux use '\n' only 
IGNORE 1 ROWS;

-------------------------------------

json.sql

--------------- MYSQL --------------
-- Converting TEXT to JSON column --

-- Return rows with invalid json
SELECT col1 FROM schema1.table1 WHERE JSON_VALID(col1) = 0;

-- Change column type to JSON
ALTER TABLE schema1.table1 CHANGE COLUMN col1 col1 JSON NOT NULL;

-------------------------------------

modify_table.sql

--------------- MYSQL ---------------
-- Note that column will be NULLABLE by default

ALTER TABLE schema1.table1 
	MODIFY col1 INTEGER;

ALTER TABLE schema1.table1 
	ADD COLUMN col1 INTEGER,
    ADD COLUMN col2 VARCHAR(10);
    
ALTER TABLE schema1.table1 
	DROP COLUMN col1,
    DROP COLUMN col2;

-------------------------------------

multiple_selects.sql

-- Multiple selects in one query

SELECT COUNT(*),
       (SELECT COUNT(*) FROM my_schema.table2) AS table2,
       (SELECT COUNT(*) FROM my_schema.table3) AS table3
FROM my_schema.table1;

procedures.sql

--------------- MYSQL --------------

-- procedure with loop

delimiter $$

DROP PROCEDURE IF EXISTS mcm.cleanup;
CREATE PROCEDURE mcm.cleanup()
wholeblock:BEGIN
  DECLARE x INT;
  SET x = 1;

  REPEAT
    
    (...)
    
    SET x = x + 1;
    UNTIL x <= 10
  END REPEAT;
END$$

CALL mcm.cleanup();

-------------------------------------

transaction_template.sql

--------------- MYSQL ---------------


SET SQL_SAFE_UPDATES = 0; 	-- Disable requirement for where clause in UPDATE/DELETE queries
SET AUTOCOMMIT = 0;			-- Turn off autocommit of each query

START TRANSACTION;



-- Verify your results and finish transaction with ROLLBACK or COMMIT
-- ROLLBACK;
-- COMMIT;

-- WARNING: 
-- Uncommit these ONLY after transaction is done!
-- Turning on AUTOCOMMIT will cause open transaction to be commited!
-- SET AUTOCOMMIT = 1;		
-- SET SQL_SAFE_UPDATES = 1;

-------------------------------------

troubleshooting.sql

--------------- MYSQL --------------
-- List locked tables
SHOW OPEN TABLES WHERE IN_USE > 0;

-- List running queries
SELECT * FROM information_schema.processlist ORDER BY id;
-- altenative
SHOW FULL PROCESSLIST;


-- list global variables
SHOW GLOBAL VARIABLES;

-- last update time for table (unaffected by select queries!)
SELECT update_time FROM information_schema.tables WHERE table_name='tablename'

-- show engine status
-- includes transactions sections (useful for killing threads)
SHOW ENGINE INNODB STATUS;

-- killing thread
KILL <THREAD_ID>;

-------------------------------------

update_substring.sql

--------------- MYSQL --------------

UPDATE schema1.table1
SET col1 = REPLACE(col1, 'find_this_string', 'replace_with_this');

-------------------------------------