For technical needs, it is required to make one of the entries in the table with id falling out of the common row, but so that it does not affect the auto_increment sequence.

-1 not suitable, as the field is unsigned

0 not suitable, because the application has checks for empty

999999 would do, but what about auto_increment?

And in general, what approach is usually practiced to solve this problem?

  • Separate column? - D-side
  • @ D-side, the goal does not justify such means. - Mik

1 answer 1

Of course, you shoot yourself in the foot, but if you really want to, you can add the line you need with a large id, and then reset the auto-increment to the old value:

ALTER TABLE `your_table` AUTO_INCREMENT = 42

And in general, what approach is usually practiced to solve this problem?

Do you need to always be at the top / bottom of a list? Make it better with an additional field "sorting" or, at worst, just a crutch in the code. What you came up with is very, very bad and someday will definitely come out sideways.

  • It will not lead to malfunction of the primary key, if its maximum value is greater than 42? In general, the question is rather about how to achieve an acceptable result and not shoot yourself a leg. And what practices are used in such cases. - Mik
  • The most reliable and easiest way to not shoot yourself a leg is not to do so. What are your technical needs? - Alexey Ukolov
  • I add a permanent test record to the table with live records. There is little data - the content is almost static (the line is added several times a month, in the future, 1-3 lines per year are planned). The management's request is to assign some kind of "non-row" id. - Mik
  • Do you need to always be at the top / bottom of a list? Make it better with an additional field "sorting" or, at worst, just a crutch in the code. What you came up with is very, very bad and someday will definitely come out sideways. - Alexey Ukolov
  • Alas, there is no question of sorting - it would be quite simple. The manual wants exactly "that the id was not like the others." Well, as I understand it, so that it can be immediately seen everywhere and everywhere that it is unusual. Yes, I suspected that this decision in practice could be harmful. I'll try to convince. - Mik