SQL gotchas

gotchas.sql

-- istest != 1 does not match null, so do this:
update payments set istest = 0 where istest is null;
-- or this:
select * from payments where coalesce(istest, 0) != 1;

-- Multiplication requires exact values; use NUMERIC or DECIMAL with length "digits,digits", eg "12,2":
ALTER TABLE payments MODIFY COLUMN payments.amount NUMERIC(12,2);

-- utf8 is not enough for emoji in MySQL; it uses "utf8mb4" for that. Fix your database:
-- https://stackoverflow.com/a/39465494/819417
SELECT concat('ALTER TABLE ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_db_name'
-- Then copy/paste all results and use a db tool or scripting language to run them one by one.
-- To fix future tables as you add them, run this as well:
ALTER DATABASE your_db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
-- general_ci matches regardless of accents (or capitals), possibly allowing spoofed accounts: https://stackoverflow.com/a/367731/819417

-- In phpMyAdmin, "CASE...END" needs "()"s, see https://stackoverflow.com/questions/43860465/case-in-the-query-gives-undefined-property-error

-- 5000+ comparison queries that time out don't get optimized, so manually add indices. Name the index to prevent adding duplicates. https://stackoverflow.com/questions/3002605/how-do-i-add-indices-to-mysql-tables#comment36572407_16362956
ALTER TABLE utmlog ADD INDEX parentid_index_name (parentid);

MySQL. Not sure how T-SQL etc handle nulls.

See also how to change columns with constraints in MySQL.