MySQL - SQL.md

SELECT

Find a record with a non-existing relation:

SELECT debate_engagements.* FROM debate_engagements
 LEFT JOIN debates ON debate_engagements.debate_id = debates.id
WHERE debates.id IS NULL;

UPDATE

Single-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_name
  SET col1 = col1 + 1, col2 = col1
  [WHERE items.id=month.id]
  [ORDER BY id DESC]
  [LIMIT _row_count_]

Multi-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table1, table2
  SET _assignment_list_
  [WHERE _where_condition_]

INSERT

insert data in a loop

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    INSERT INTO widget_answers (widget_id, answers_hash, date, created_at, updated_at, engagement_started_at, engagement_ended_at, engagement_id, result_id, hosting_url, test) SELECT widget_id, answers_hash, date, created_at, updated_at, engagement_started_at, engagement_ended_at, engagement_id, result_id, hosting_url, test FROM widget_answers WHERE id = 1;
    SET p1 = p1 + 1;
    IF p1 < 3000 THEN
      ITERATE label1;
    END IF;

    LEAVE label1;
  END LOOP label1;
END;

CALL doiterate(0);
DROP PROCEDURE IF EXISTS doiterate;

DELETE

DB stats

table sizes:

SELECT
  table_schema as `Database`,
  table_name AS `Table`,
  (DATA_LENGTH + INDEX_LENGTH) / POWER(1024,2) `Size in MB`
FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC;

SELECT TABLE_NAME AS 'Table',
      (DATA_LENGTH + INDEX_LENGTH) / POWER(1024,2) AS `Size (MB)`
FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE()
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

database default character set & collation

SELECT @@character_set_database, @@collation_database;

show default character set & collation for all databases:

SELECT SCHEMA_NAME 'database',
       default_character_set_name 'charset',
       DEFAULT_COLLATION_NAME 'collation'
FROM information_schema.SCHEMATA;

show collation for the table:

SHOW FULL COLUMNS FROM my_tablename;

show columns charset:

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.Columns WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tbl_name';

DDL

re-order columns

ALTER TABLE tbl MODIFY COLUMN `meta` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL FIRST, ALGORITHM=INPLACE, LOCK=NONE;
-- or:
ALTER TABLE `widget_interactions` MODIFY COLUMN meta varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER engagement_id, ALGORITHM=INPLACE, LOCK=NONE;

remove NULL constraint

ALTER TABLE table MODIFY COLUMN column int;