In MySQL data table, should a self-increasing field such as user id be numeric or character type? What are the advantages and disadvantages of each?

  mysql, question

For example, if you design a table, how do you choose the type of id field?

Use a self-increasing integer.


IdGenerally speaking, it will not be negative, so useUNSIGNED.
IdIt is equivalent to ID card and should not be NULL.
As for why it is an integer, it is related to two factors:

  1. Occupy space.

  2. Efficiency.

  3. MySQL’sAUTO_INCREMENTCharacter type is not supported.
    Needless to say, the occupied space,IntFixed Type Occupies Only4 bytesThe scope of expression has reached-2 31 (-2,147,483,648) to 2 31–1 (2,147,483,647), set to non-negative and then double, while using a string to represent such a large amount of data …

Efficiency is related to index structure, MySQL is usedB plus treeAs the data structure of the index, if a self-increasing integer is used, the insertion of data will only cause the node at mostSplitHowever, using a string is likely to be inserted anywhere, which means that a node’sMovement and division. Secondly, when querying data, string comparison is slower than integer comparison.

For more information, please refer to this:
Does Self-Increasing Primary Key Decrease Database insert Performance? If so, why are many other companies adopting it?

Finally: I support that the primary key has nothing to do with specific data = = .. so using integer as the primary key is a good choice.