The difference between MySQL int (3) and int (11)

Source: Internet
Author: User

Summing up, int (m) Zerofill, plus Zerofill after M to show a little bit of effect, such as int (3) Zerofill, you inserted into the database is 10, then the actual insert is 010, that is, add a 0 in front. if int (3) and int (10) Without Zerofill, they are no different. M is not used to limit the number of int. The maximum and minimum values of int (M) are related to undesigned, the bottom of which is illustrated.

Mysql> CREATE table t (t Int (3) zerofill);
Query OK, 0 rows Affected (0.00 sec)

mysql> INSERT INTO t set t = 10;
Query OK, 1 row Affected (0.00 sec)

Mysql> select * from T;
+--+
| T |
+--+
| 010 |
+--+
1 row in Set (0.11 sec)

Zerofill with default width, the same as int (10):

Mysql> CREATE table t (t int zerofill);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t set t = 10;
Query OK, 1 row affected (0.02 sec)

Mysql> select * from T;
+ ———— +
| T |
+ ———— +
| 0000000010 |
+ ———— +
1 row in Set (0.08 sec)

Without Zerofill:

Mysql> CREATE table t (t int);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t set t = 10;
Query OK, 1 row affected (0.01 sec)

Mysql> select * from T;
+--+
| T |
+--+
| 10 |
+--+

1 row in Set (0.00 sec)



Reproduced above from http://hi.baidu.com/nancy0754/blog/item/2f3ea13a957a27d7d5622535.html

1 bytes = 8 bit, a byte can represent a maximum of 2 of the length of the data 8 square 11111111 in the computer is also

128 to 127

1.BIT[M]

Bit field type, M represents the number of bits per value, range from 1 to 64, if M is ignored, default is 1

2.tinyint[(m)] [UNSIGNED] [Zerofill] M default is 4

A small integer. The signed range is 128 to 127. The unsigned range is 0 to 255.

3. Bool,boolean

is a synonym for tinyint (1). The zero value is considered false. Non-zero values are considered true.

4.smallint[(m)] [UNSIGNED] [Zerofill] M default is 6

A small integer. The signed range is 32768 to 32767. The unsigned range is 0 to 65535.

5.mediumint[(m)] [UNSIGNED] [Zerofill] M default is 9

A medium-sized integer. The signed range is 8388608 to 8388607. The unsigned range is 0 to 16777215.

6. int[(m)] [UNSIGNED] [Zerofill] M default is 11

An integer of normal size. The signed range is 2147483648 to 2147483647. The unsigned range is 0 to 4294967295.

7.bigint[(m)] [UNSIGNED] [Zerofill] M default is 20

A large integer. The signed range is 9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.

Note: M here is not a specific length stored in the database, it was always mistaken for int (3) to store only 3-length numbers, and int (11) would store 11-length numbers, which is a big mistake.

In fact, when we choose the type of int, whether it is int (3) or int (11), it is stored in the database is 4 bytes of length, when using int (3) If you enter 10, will default to you storage bit 010, This means that the 3 is the default length, and when you are less than 3, it will help you not all, when you have more than 3 bits, there is no effect.

The day before yesterday group Tube asked me what is the difference between int (10) and int (11), then think is the difference between the length, now look, they are in addition to the time of storage in a slightly different, when we use the time there is no difference. an int (10) can also represent a value of 2147483647 for which int (11) can also be represented.

To see the different effects remember to add the Zerofill value when creating the type, indicating that it is filled with 0, otherwise the effect is not visible.

We don't usually add this option when we create a database, so we can say there's no difference between them.

Transferred from: http://blog.sina.com.cn/s/blog_610997850100wjrm.html

Blogger Supplement:

Q:mysql int type is not up to 4,294,967,295, how to create a new table, the default is not to write the number after the int (that is, do not write int (5)), the table will be built after the INT (11)

A: The reason is that if you do not specify unsigned, the minus sign is one digit.

The difference between MySQL int (3) and int (11)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.