MySQL数据表中，用户id这样的自增字段，应该是数字型还是字符型？各有什么优缺点？

Question

如题，设计一张表，id这种自增字段该怎么选择类型？

Answer 1

Use self-increasing integers.

id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT

id is generally not a negative number, so use UNSIGNED. id一般来说不会是负数，所以用UNSIGNED。
id相当于身份证，不应该也不能是NULL。
至于为什么是整数，和两个因素有关：

1. 占用空间。

2. 效率。

3. MySQL的AUTO_INCREMENT不支持字符型。
   占用空间不用多说， INT类型固定只占用4个字节，能表示的范围达到了-2^31 (-2,147,483,648) 到 2^31 – 1 (2,147,483,647) ，设为非负之后翻倍，而使用字符串想要表示这么大量的数据...

效率则和索引的结构有关，MySQL使用B+树作为索引的数据结构，如果使用自增整数的话，插入数据时最多只会引起节点的分裂，而使用字符串则有可能会插入到任何地方，这意味着可能会引起节点的移动和分裂id is equivalent to an ID card and should not and cannot be NULL.

As for why it is an integer, it is related to two factors:

1. 
   Takes up space.

2. Efficiency.

3. 🎜MySQL's AUTO_INCREMENT does not support character types. 🎜Needless to say, it takes up space. The INT type only occupies 4 bytes, and the representable range reaches -2^31 (-2,147,483,648) to 2 ^31 – 1 (2,147,483,647) , doubled after being set to non-negative, and using strings to represent such a large amount of data...🎜

🎜Efficiency is related to the structure of the index. MySQL uses B+ tree as the data structure of the index. If you use auto-increasing integers, inserting data will only cause splitting of nodes at most. , and using strings may be inserted anywhere, which means that it may cause moving and splitting of nodes. Secondly, during data query, string comparison is slower than integer comparison. 🎜 🎜For more information, please refer to this: 🎜Will auto-incrementing primary keys reduce database insert performance? If so, why do so many companies still use it? 🎜 🎜Finally: I support that the primary key has nothing to do with the specific data = =.. So using integers as the primary key is a better choice. 🎜

Answer 2

Numerical type for convenient indexing