Edit to spec as necessary: DELIMITER $$ CREATE PROCEDURE trimTable() BEGIN DECLARE ROWS INT; DECLARE rows_deleted INT; SET ROWS = 1; SET rows_deleted = 10000; WHILE ROWS > 0 DO DELETE FROM db.tabA WHERE predicate_col < CURDATE() - INTERVAL 90 DAY ORDER BY `id` LIMIT 10000; SET ROWS = ROW_COUNT(); SET rows_deleted = rows_deleted + ..
Category : SQL
Suppose I try to create a table with a primary key that’s varchar(500), and MySQL complains the key length is longer than the maximum of 1000 bytes. 500 is less than 1000. What’s happening? Plus, a tasty (yet apparently harmless) bug in MySQL. Here’s a statement that will fail on most servers: CREATE TABLE test(c ..
These are online, self-guided ways for developers wishing to gain more RDBMS understanding (or have a refresher course) in the two most ubiquitous platforms — MySQL and PostgreSQL. I add and remove from this list from time to time as material becomes outdated or better bits are found. Disclaimer: I didn’t have a hand in ..
If ever there was a TL;DR, this is it. However, it is important to understand the “whys” in my world, so here goes. A full discussion of character sets and encoding is beyond the scope of this document. (If you want more background, I recommend checking out the wikipedia article for a good place to ..