MySQL technology insider-SQL programming Reading Notes (2)-data types, java programming ideas Reading Notes

MySQL technology insider-SQL programming Reading Notes (2)-data types, java programming ideas Reading Notes

The selection of data types affects the performance of applications that interact with the database.

1. Generally, if a page can store as many rows as possible, the database performance will be better. Therefore, it is vital to select a correct data type.

2. If the wrong data type is selected when creating a TABLE in the database, the maintenance cost may be very high. You need to spend a lot of time performing the alter table operation.

1. Data Type

1. UNSIGNED

The numeric type is not symbolic.

For example:

The INT type range is-2147483648 ~ 2147483647

The int unsigned type ranges from 0 ~ 42967295

It looks good, but it has some negative effects.

 

CREATE TABLE t (a INT UNSIGNED,b INT UNSIGNED);INSERT INTO t SELECT 1,2;SELECT a - b FROM t;

An error is reported after the last SQL statement is executed. If no error is reported, the result is 4 294 967 295.

The reason for this is:

 

-The hexadecimal representation of 1 is: 0 xfff fff ff
4 294 967 295 in hexadecimal format: 0 xfff fff ff
In MySQL databases, the return values of UNSIGNED operations are all UNSIGNED.

If you want to get the value-1, you only need to set the SQL _MODE parameter:

SET sql_mode='NO_UNSIGNED_SUBTRACTION';

 

Try not to use UNSIGNED. INT type data may not be stored, and int unsigned may not be. In this case, it is better to upgrade INT type to BIGINT type during database design.

 

2. ZEROFILL

Such as a display property.

 

ALTER TABLE t CHANGE COLUMN a a int(4) UNSIGNED ZEROFILL;

After column a is modified, the data in the t table will be displayed differently:

 

SELECT a,b FROM t\G;a: 0001b:2

 

We can see that the value of a is changed from 1 to 0001, which is the role of the ZEROFILL attribute. If the width is smaller than the Set width, it is automatically filled with 0.

Note:

 

In MySQL, the actual storage is still 1. You can use the HEX function to check:

SELECT a,HEX(a) FROM t\G; 

 

Some windows may not be seen, but mysql command line can be seen.