Category: MySQL

mySQL / mariadb reset primary key

ALTER TABLE members DROP ID;
ALTER TABLE members AUTO_INCREMENT = 1;
ALTER TABLE members ADD ID int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

Database Restored

Due to my own stupidity I had to restore a backup from 8.11. Some new codes might be lost for the time but data will eventually feed back into the system. Sorry about the inconvenience.

mySQL trim leading zeros

UPDATE table SET column= TRIM(LEADING '0' FROM column) WHERE column LIKE '0%'

MySQL conditional updates

INSERT INTO daily_events
  (created_on, last_event_id, last_event_created_at)
VALUES
  ('2010-01-19', 23, '2010-01-19 10:23:11')
ON DUPLICATE KEY UPDATE
  last_event_id = IF(last_event_created_at < VALUES(last_event_created_at), VALUES(last_event_id), last_event_id),
  last_event_created_at = IF(last_event_created_at < VALUES(last_event_created_at), VALUES(last_event_created_at), last_event_created_at);

Source: https://thewebfellas.com/blog/conditional-duplicate-key-updates-with-mysql/

Back to Top