0

I have a reason that don't want any 0 in my table id, so I want to skip any id that contain a 0 on auto_increment, is that possible? When the id is 10, it will skip it and insert with id 11, if id is 200 or 3001, it will skip it and create 211 and 3111.

conan
  • 1,327
  • 1
  • 12
  • 27
  • why don't want zero is there any logic ? – JYoThI Sep 09 '16 at 04:39
  • I am trying to create a relational table, that use use this id number for another table, and then combine with 0, like 4508090 in the second table, and then I can separate them with 0. I know I can separate with any symbol like -, but I want to use int instead of varchar to save space – conan Sep 09 '16 at 04:51
  • I think this [link](http://stackoverflow.com/questions/5228408/mysql-auto-increment-custom-values) will helps you. – Geeky Ninja Sep 09 '16 at 04:54
  • I could do this but it would take an hour, would not be an auto_increment, and the inserts would have to happen thru a stored proc – Drew Sep 09 '16 at 05:12
  • not a proper solution but simply get max() from table replace 0 with 1 and then perform insert with specifying primary key and it's value. – siddhesh Sep 09 '16 at 05:17
  • create dummy table with auto_increment and get the id from the table and insert into your main table which the filed set as a primary key and int . so now come to your case . if id 10 means you can change it with str_replace('0','1','10'); so you get 11 and insert into your table – JYoThI Sep 09 '16 at 05:18
  • 1
    Normalize your database and use a many-to-many pivot table. – tkausl Sep 09 '16 at 05:55
  • Transform the numbers to octal and add one to each digit? (Or base nine, but that's even more arcane than octal.) – tripleee Sep 09 '16 at 06:03
  • 1
    looks like you have the wrong design – e4c5 Sep 10 '16 at 10:11
  • I also think you should refactor your structure. But if you have no other choice you can take a look into triggers. Maybe you can use a trigger that fixes your new IDs. – AbcAeffchen Sep 10 '16 at 18:13

0 Answers0