MySQL Technology insider--sql Programming reading notes (ii)--data types

The choice of data types will affect the performance of the application interacting with the database.

1. In general, if you can store as many rows as possible within a page, the better the database performance, so choosing the right data type is critical.

2. On the other hand, if you select the wrong data type when you create a table in the database, later maintenance costs can be very large, and the user spends a lot of time on the ALTER TABLE operation.

1. Data type

1. UNSIGNED

The number type is not symbolized.

For example:

The type range of INT is-2147483648 ~ 2147483647

The type range of INT UNSIGNED is 0 ~ 42967295

Looks like a nice property, but it has some negative effects.

CREATE TABLE INT INT UNSIGNED); INSERT  into SELECT 1,2; SELECT -  from T;

Execution of the last SQL will result in an error, if no error is obtained should be 4 294 967 295.

This result is due to:

-1 of the 16 binary representation is: 0XFFF FFF FF
4 294 967 295 of the 16 binary representation is: 0XFFF FFF FF
In the MySQL database, the return value is unsigned for the unsigned number of operations.

If you want to get the value 1, just set the Sql_mode parameter:

SET Sql_mode='no_unsigned_subtraction ';

Try not to use data that the Unsigned,int type may not hold, and int UNSIGNED may not be able to store it, so it is better to promote the int type to the bigint type in the database design phase.

2, Zerofill

Is like a displayed property.

ALTER TABLE COLUMN int (4) UNSIGNED Zerofill;

After the column A has been modified, the data for the T-table is searched, and a different display appears:

SELECT  from  0001B:2

You can see that the value of a is changed from 1 to 0001, which is what the Zerofill property does, and if the width is less than the set width, it is automatically populated with 0.

Attention:

The actual storage in MySQL is still 1. You can check with the hex function:

SELECT  from

The MySQL command line can be seen in some windows that may not be visible.

MySQL Technology insider--sql Programming reading notes (ii)--data types