Tips and Tricks About MySQL

Hi guys, long time no post again about programming. Now I will share you about some tricks on mysql. I hope this was useful for you. Okay let's see the code

1. Reindexing an Auto Increment Id

ALTER TABLE yourtable ADD COLUMN id2 INT(11) UNSIGNED  NOT NULL AFTER `id`;     SET @a:=0;                                                                      UPDATE yourtable SET id2=@a:=@a+1 ORDER BY id;                                   ALTER TABLE yourtable DROP id;                                                  ALTER TABLE yourtable CHANGE id2 id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,   ADD PRIMARY KEY(id);

2. Make a zerofill on the field value

CREATE TABLE yourtable (x INT(8) ZEROFILL NOT NULL, y INT(8) NOT NULL);
INSERT INTO yourtable (x,y) VALUES
(1, 1),
(12, 12),
(123, 123),
(123456789, 123456789);
SELECT x, y FROM yourtable;