Mysql 简明教程

MySQL - INT

The MySQL INT Data Type

MySQL INT 数据类型用来存储没有小数位（整数）的整数。

The MySQL INT data type is used to store whole numbers without the decimal places (integers).

然而，MySQL 提供了不同的整数数据类型，如 TINYINT、SMALLINT、MEDIUMINT 和 BIGINT，以满足不同范围的整数。

However, MySQL provides various integer data types, such as TINYINT, SMALLINT, MEDIUMINT, and BIGINT to cater to different ranges of whole numbers.

以下表格说明了 MySQL 中不同整数类型在字节、最小值和最大值（带符号和不带符号选项）方面的特征 −

The following table illustrates the characteristics of different integer types in MySQL, including storage in bytes, minimum value, and maximum value for both signed and unsigned options −

Type

Storage (Bytes)

Minimum value (Signed/Unsigned)

Maximum value (Signed/Unsigned)

TINYINT

-128/ 0

127/ 255

SMALLINT

-32768/ 0

32767/ 65535

MEDIUMINT

-8388608/ 0

8388607/ 16777215

INT

-8388607/ 16777215

2147483647/ 4294967295

BIGINT

- 9223372036854775808 / 0

9223372036854775807 / 18446744073709551615

我们必须根据要存储的数据类型（类型）选择数据类型。如果可能，我们需要使用较小的数据类型以减小数据库大小。TINYINT 可用于小数，而 INT 或 BIGINT 用于大数，例如一个国家中的电话号码。

We have to choose the data types based on the kind (type) of data being stored. If possible, we need to use smaller data types to minimize the database size. TINYINT can be used for small numbers, while INT or BIGINT is used for large numbers like phone numbers in a country.

Auto Increment with MySQL INT

在 MySQL 中，你可以将 AUTO_INCREMENT 特性与 INT 列一起使用，为该列自动生成唯一值。以下是它的工作原理 −

In MySQL, you can use the AUTO_INCREMENT attribute with an INT column to automatically generate unique values for that column. Here’s how it works −

Initial Value − When you create a table with an AUTO_INCREMENT INT column, the sequence starts with 1.
Inserting NULL or 0 − When you insert a record with a NULL or 0 value for the AUTO_INCREMENT column, MySQL sets the value to the next sequence value. This means it assigns the next available integer starting from 1.
Inserting Non-NULL Values − If you insert a non-NULL value into the AUTO_INCREMENT column, MySQL accepts that value and continues the sequence based on the new value inserted.

Example

首先，我们使用名为 "ID" 的 AUTO_INCREMENT INT 列创建一个名为 STUDENTS 的表 −

First of all, we are creating a table named STUDENTS with an AUTO_INCREMENT INT column named "ID" −

CREATE TABLE STUDENTS (
   ID int auto_increment,
   NAME varchar(20),
   primary key (ID)
);

当我们在该表中插入记录且不指定 "ID" 列的值时，MySQL 自动为 "ID" 生成从 1 开始的唯一值。

When we insert records into this table without specifying values for the "ID" column, MySQL automatically generates unique values for "ID" starting from 1.

在此，我们使用如下 INSERT 查询在 STUDENTS 表中插入三行 −

Here, we are inserting three rows into the STUDENTS table using the below INSERT query −

INSERT INTO STUDENTS (NAME) VALUES
('Tilak'), ('Akash'), ('Surya'), ('Deepak');

创建的 STUDENTS 表如下 −

The STUDENTS table created is as follows −

现在，让我们插入一行，我们为 "ID" 列提供一个明确的值 −

Now, let us insert a row where we provide an explicit value for the "ID" column −

INSERT INTO STUDENTS (ID, NAME) VALUES (15, 'Arjun');

以下是所获得的输出 −

Following is the output obtained −

Query OK, 1 row affected (0.01 sec)

由于我们指定 "ID" 为 15，因此 MySQL 将该序列重置为 16。如果我们在不指定 "ID" 的情况下插入新行，MySQL 会使用 16 作为下一个 AUTO_INCREMENT 值 −

Since we specified the "ID" as 15, MySQL resets the sequence to 16. If we insert a new row without specifying the "ID," MySQL will use 16 as the next AUTO_INCREMENT value −

INSERT INTO STUDENTS (NAME) VALUES ('Dev');

获得的输出如下 −

The output obtained is as follows −

Query OK, 1 row affected (0.01 sec)

现在，让我们从 "STUDENTS" 表中检索记录 −

Now, let us retrieve the records from the "STUDENTS" table −

SELECT * FROM STUDENTS;

生成的表为 −

The table produced is −

MySQL INT UNSIGNED

在 MySQL 中，当你针对列定义一个 UNSIGNED INT 时，该列仅限存储非负值（即正值）。在这些列中不允许负值。

In MySQL, when you define an UNSIGNED INT on a column, that column is restricted to storing only non-negative values (i.e., positive values). Negative values are not allowed in such columns.

Example

让我们使用以下查询创建一个名为 EMPLOYEES 的表 −

Let us create a table with the name EMPLOYEES using the following query −

CREATE TABLE EMPLOYEES (
   ID int auto_increment,
   NAME varchar(30) not null,
   AGE int UNSIGNED,
   Primary key(ID)
);

以下是所获得的输出 −

Following is the output obtained −

Query OK, 0 rows affected (0.04 sec)

现在，让我们向“AGE”列中插入一个非负值——

Now, let us insert a row with a non-negative value into the "AGE" column −

INSERT INTO EMPLOYEES (NAME, AGE) VALUES ('Varun', 32);

上面的查询将顺利执行，因为为“AGE”列提供的值是非负的。

The above query will execute successfully since the value provided for the "AGE" column is non-negative.

Query OK, 1 row affected (0.01 sec)

但是，如果我们尝试向“AGE”列中插入一个负值，MySQL 将会生成一个错误——

However, if we attempt to insert a negative value into the "AGE" column, MySQL will generate an error −

INSERT INTO EMPLOYEES (NAME, AGE) VALUES ('Sunil', -10);

MySQL 将发出如下所示的错误——

MySQL will issue an error as shown below −

ERROR 1264 (22003): Out of range value for column 'AGE' at row 1

MySQL INT with Display Width Attribute

在 MySQL 中，您可以通过在 INT 关键字后面使用圆括号来指定 INT 数据类型的显示宽度。例如，使用 INT(5) 将显示宽度设置为五位数字。

In MySQL, you can specify a display width for the INT data type by using parentheses after the INT keyword. For instance, using INT(5) sets the display width to five digits.

需要注意的是，MySQL 中 INT 的显示宽度属性不会影响列中可以存储的值的范围。它会格式化应用程序中的整数值，并作为元数据包含在结果集中。

It’s important to note that the display width attribute for INT in MySQL doesn’t affect the range of values that can be stored in the column. It formats integer values in applications, and is included as metadata in the result set.

例如，如果您在 EMPLOYEES 表的 id 列中插入值 12345，它将按原样存储。当您检索它时，一些应用程序可能会选择用前导零填充它，以确保它以五位数字显示（例如，012345）。

For example, if you insert the value 12345 into the id column of the EMPLOYEES table, it will be stored as is. When you retrieve it, some applications may choose to pad it with leading zeros to ensure it is displayed as five digits (e.g., 012345).

MySQL INT with ZEROFILL Attribute

在 MySQL 中，ZEROFILL 属性是一个非标准属性，可以应用于数字数据类型。它会向显示值添加前导零，确保数字以固定的宽度显示，这对于数字代码特别有用。

In MySQL, the ZEROFILL attribute is a non-standard attribute that can be applied to numeric data types. It adds leading zeros to the displayed values, making sure the number is displayed with a fixed width, especially useful for numerical codes.

Example

让我们创建一个名为 ZEROFILL_TABLE 的表，使用以下查询对 INT 列应用 ZEROFILL——

Let us create a table with the name ZEROFILL_TABLE with ZEROFILL applied to INT columns using the query below −

CREATE TABLE ZEROFILL_TABLE (
   col1 int(4) ZEROFILL,
   col2 int(6) ZEROFILL,
   col3 int(8) ZEROFILL
);

获得的输出如下 −

The output obtained is as follows −

Query OK, 0 rows affected, 6 warnings (0.02 sec)

现在，我们正在向上面创建的表中插入新行——

Now, we are inserting a new row into the above-created table −

INSERT INTO ZEROFILL_TABLE (col1, col2, col3)
VALUES (1, 7, 3);

以下是上面代码的输出： -

Following is the output of the above code −

Query OK, 1 row affected (0.00 sec)

现在，让我们显示 ZEROFILL_TABLE 表中的记录——

Now, let us display the records from the ZEROFILL_TABLE table −

SELECT * FROM ZEROFILL_TABLE;

我们可以在下面的输出中看到，值以指定宽度显示，并且会添加前导零以保持该宽度，正如 ZEROFILL 属性所确定的那样——

We can see in the output below, the values are displayed with the specified width, and leading zeros are added to maintain that width, as determined by the ZEROFILL attribute −

INT Datatype Using a Client Program

我们还可以使用客户端程序创建 INT 数据类型的列。

We can also create column of the INT datatype using the client program.

Syntax

Example

以下是这些程序 −

Following are the programs −