Numeric types in column types in MySQL tutorial

This article mainly introduces you to the relevant knowledge points of the numerical column type in MySQL. I hope it will be helpful to friends in need!

Recommended reference tutorial: "mysql tutorial"

Column type (data type)types in column types in MySQL tutorial>

The so-called column type actually refers to data Type, that is, a unified classification of data, from a system perspective is to be able to manage it in a unified way and make better use of limited space.

In SQL, data types are divided into three major categories: Numeric type, string type and date and time type.

For numeric data, it can be further divided into integer type and decimal type.

Integer typetypes in column types in MySQL tutorial>

In SQL, due to the issue of saving disk space, the system subdivides the integer type into five categories, namely:

• tinyint: Mini integer, uses Numeric types in column types in MySQL tutorial byte to store data (commonly used);

• smallint: Small integer , uses Numeric types in column types in MySQL tutorial bytes to store data;

• mediumint: medium integer type, uses Numeric types in column types in MySQL tutorial bytes to store data;

• int: Standard integer type, uses Numeric types in column types in MySQL tutorial bytes to store data (commonly used);

• bigint: Large integer type, Use Numeric types in column types in MySQL tutorial bytes to store data.

Next, enter the following SQL statement for testing:

-- 创建整型表create table my_int(
    int_Numeric types in column types in MySQL tutorial tinyint,
    int_Numeric types in column types in MySQL tutorial smallint,
    int_Numeric types in column types in MySQL tutorial int,
    int_Numeric types in column types in MySQL tutorial bigint
)charset utfNumeric types in column types in MySQL tutorial;

As shown in the picture above, we have successfully created my_int table, and then insert data:

-- 插入数据insert into my_int values (Numeric types in column types in MySQL tutorial,Numeric types in column types in MySQL tutorial,Numeric types in column types in MySQL tutorial,Numeric types in column types in MySQL tutorial);insert into my_int values (&#Numeric types in column types in MySQL tutorial9;a&#Numeric types in column types in MySQL tutorial9;,&#Numeric types in column types in MySQL tutorial9;b&#Numeric types in column types in MySQL tutorial9;,&#Numeric types in column types in MySQL tutorial9;c&#Numeric types in column types in MySQL tutorial9;,&#Numeric types in column types in MySQL tutorial9;d&#Numeric types in column types in MySQL tutorial9;);insert into my_int values (Numeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorial,Numeric types in column types in MySQL tutorial,Numeric types in column types in MySQL tutorial,Numeric types in column types in MySQL tutorial);

As shown in the figure above, through the column type, we can limit the type and length range of the inserted data.

As for why an out-of-range error is reported when assigning a value to int_Numeric types in column types in MySQL tutorial, it is because the numerical type in SQL has a signed bit by default, that is, positive and negative. If we need to use unsigned data, we need to declare the data type ourselves, that is, append the unsigned keyword when declaring the data type. For example:

-- 在 my_int 表中，添加 int_Numeric types in column types in MySQL tutorial 字段，设置其数据类型为 tinyint unsignedalter table my_int add int_Numeric types in column types in MySQL tutorial tinyint unsigned;

As shown in the picture above, the int_Numeric types in column types in MySQL tutorial field is added successfully, continue to insert data:

-- 插入数据insert into my_int values (Numeric types in column types in MySQL tutorial,Numeric types in column types in MySQL tutorial,Numeric types in column types in MySQL tutorial,Numeric types in column types in MySQL tutorial,Numeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorial);

As shown in the picture above, when we limit tinyint to unsigned, we can already insert any integer between 0~Numeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorial! However, looking back, let us take a closer look at the following picture:

By observing the above picture, we will find: The data of each field The type will be followed by a pair of parentheses containing numbers. These numbers actually have no special meaning, they just represent the display width of the data. In fact, we can modify the display width, but this modification will not change the size of the data itself.

The meaning of display width: When the data is not enough to display the width, the data will automatically change to the corresponding display width. Usually it needs to be matched with a leading 0 Increase the width without changing the size of the data value, that is, use zerofill to zero-fill, and zero-filling will cause the value to automatically become unsigned.

Next, execute the following SQL statement:

-- 在 my_int 表中，添加 int_Numeric types in column types in MySQL tutorial 字段，设置其数据类型为 tinyint zerofillalter table my_int add int_Numeric types in column types in MySQL tutorial(Numeric types in column types in MySQL tutorial) tinyint zerofill;

Then insert data and test:

-- 插入数据insert into my_int values (Numeric types in column types in MySQL tutorial,Numeric types in column types in MySQL tutorial,Numeric types in column types in MySQL tutorial,Numeric types in column types in MySQL tutorial,Numeric types in column types in MySQL tutorial,Numeric types in column types in MySQL tutorial);

As shown in the figure above, The meaning of zero padding is to ensure the format of the data.

Decimal type

Decimal type, that is, numeric type with decimal point or range beyond integer type.

In SQL, the decimal type is subdivided into two types: Floating point type and Fixed point type, among which:

• Floating point type: floating decimal point, limited precision, easy to lose precision;

• Fixed point type: fixed decimal point, fixed precision, no precision loss.

Type Numeric types in column types in MySQL tutorial: Floating point type

Floating point type data is a type of precision data, because after exceeding the specified range, it will Precision is lost and rounding is performed automatically. Theoretically, floating point types are divided into two types of precision:

• float: single precision, occupies Numeric types in column types in MySQL tutorial bytes to store data, and the precision range is about Numeric types in column types in MySQL tutorial digits. ;

• double：双精度，占用 Numeric types in column types in MySQL tutorial 个字节存储数据，精度范围大概为 Numeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorial 位左右。

浮点型的使用方式：如果直接用float，则表示没有小数部分；如果用float(M,D)，其中M代表总长度，D代表小数部分长度，M-D则为整数部分长度。

执行如下 SQL 语句创建浮点数表，进行测试：

-- 创建浮点数表create table my_float(
    fNumeric types in column types in MySQL tutorial float,
    fNumeric types in column types in MySQL tutorial float(Numeric types in column types in MySQL tutorial0,Numeric types in column types in MySQL tutorial),
    fNumeric types in column types in MySQL tutorial float(Numeric types in column types in MySQL tutorial,Numeric types in column types in MySQL tutorial)
)charset utfNumeric types in column types in MySQL tutorial;

在咱们向浮点数表my_float插入数据的时候，可以直接插入小数，也可以插入用科学计数法表示的数据。此外，插入浮点型数据时，整数部分是不能超出长度范围的，但是小数部分是可以超出长度范围的，系统会自动进行四舍五入的操作。特别是，如果浮点数是因为系统进位（四舍五入）导致整数部分超出指定的长度，那么系统是允许成立的。

-- 插入测试数据insert into my_float values (Numeric types in column types in MySQL tutorial.Numeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialeNumeric types in column types in MySQL tutorial,Numeric types in column types in MySQL tutorial0.Numeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorial,9999.99);insert into my_float values (Numeric types in column types in MySQL tutorial0Numeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorial0,Numeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorial9.99,9999.99);insert into my_float values (Numeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorial,Numeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorial.99,99.99999);

如上图所示，咱们的结论得到了验证。

第 Numeric types in column types in MySQL tutorial 种：定点型

定点型数据，绝对的保证整数部分不会被四舍五入，也就是说不会丢失精度，但小数部分有可能丢失精度，虽然理论上小数部分也不会丢失精度。

执行如下 SQL 语句创建定点数表，以浮点数做对比，进行测试：

-- 创建定点数表create table my_decimal(
    fNumeric types in column types in MySQL tutorial float(Numeric types in column types in MySQL tutorial0,Numeric types in column types in MySQL tutorial),
    dNumeric types in column types in MySQL tutorial decimal(Numeric types in column types in MySQL tutorial0,Numeric types in column types in MySQL tutorial)
)charset utfNumeric types in column types in MySQL tutorial;

当咱们插入数据的时候，定点数的整数部分一定不能超出长度范围（进位也不可以），小数部分的长度则可以随意超出，没有限制，系统会自动进行四舍五入的操作：

-- 插入测试数据insert into my_decimal values (99999999.99,99999999.99);insert into my_decimal values (Numeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorial9.99,Numeric types in column types in MySQL tutorial0Numeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorial.Numeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorial);insert into my_decimal values (Numeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorial.99,Numeric types in column types in MySQL tutorial0Numeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorial.Numeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorialNumeric types in column types in MySQL tutorial);

如上图所示，咱们的结论同样得到了验证。

The above is the detailed content of Numeric types in column types in MySQL tutorial. For more information, please follow other related articles on the PHP Chinese website!