Month: June 2022

Posted on

mysql | memo

case: remove a row with FOREIGN_KEY

SET FOREIGN_KEY_CHECK = 0;
DELETE FROM `tableName` WHERE (id = `idNumber`);
SET FOREIGN_KEY_CHECK = 1;

case: reset AUTO_INCREMENT id MAX

ALTER TABLE `tableName` AUTO_INCREMENT = `value`;

The ‘value’ is where the AUTO_INCREMENT starts next time.

SELECT @max := MAX(ID) + 1 FROM `tableName`;
PREPARE stmt FROM 'ALTER TABLE `tableName` AUTO_INCREMENT = ?';
EXECUTE stmt USING @max;
DEALLOCATE PREPARE stmt;

case: unable to enter `emptyValue` ” to a NULL space. (once rebooted mySQL, run the command)

SET @@global.sql_mode = '';