MySQL - Auto_increment by 10

How do I set the au开发者_如何学JAVAto_increment column in such a way that it increments by 10 for each record insert.

For eg: Instead of the default auto_increment by 1,

insert into temptable (name) values('abc'),('def');

select * from temptable;
  id|name
  1|abc
  2|def

auto_increment by 10,
  id|name
  10|abc
  20|def

You would have to change the auto_increment_increment setting, however this would apply to all tables at once. I don't think there is a per-table increment setting.

This is usually not really a good idea to do, though. Are you looking to generate invoice numbers or something? In that case, you may be better off using a custom method that generates the next number.

I'm not sure why do you need that, but if you want to have this result on output you can simply multiply value by 10

You could turn off the auto_increment, and add a trigger. Set up another table with a single value, the next id, and then set the id of an inserted row to the value of that table:

CREATE TABLE next (next_id int not null);
INSERT INTO next VALUES(0);

delimiter |

CREATE TRIGGER update_id BEFORE INSERT ON temptable
  FOR EACH ROW BEGIN
    SET NEW.id = (SELECT next_id FROM next);
    UPDATE next SET next_id = next_id + 10;
  END;
|
delimiter ;

If you already have your ids and want to enlarge them by '10 by 10', follow Nazariy's idea and use the current ids and multiply them by 10 with

update xyourtablex set id=id*10000

or a bigger number than your ids (if not, you will create duplicates) and finally:

update xyourtablex set id=id/1000

or simply remove unique or index, multiply by 10 and add index or unique again :)