Shulou(Shulou.com)05/31 Report--

MySQL database table design skills, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

Selection of 1.int types

The integer field types include tinyint, smallint, mediumint, int and bigint. The occupied space and storage range are shown in the following figure:

The smaller the storage bytes, the smaller the footprint. Therefore, in line with the principle of minimizing storage, we should try to choose the appropriate integer, here are a few common cases and selection suggestions.

Choose the appropriate type according to the storage area, such as unsigned tinyint for human age (range 0,255, human lifespan will not exceed 255years); sea turtles must be smallint, but if it is the age of the sun, it must be int.

If the stored data is non-negative, it is recommended to use UNSIGNED identification, which can expand the storage range of positive numbers.

Short data use TINYINT or SMALLINT, such as human age, city code.

The field in which the status variable is stored is TINYINT. For example, whether to delete it or not. 0 means not deleted. 1 means it has been deleted.

Primary key column, no negative number. It is recommended to use INT UNSIGNED or BIGINT UNSIGNED; to estimate that the numerical value of the field will exceed 4.2 billion, using the BIGINT type.

The following is a demonstration of the construction table statement:

CREATE TABLE `tb_ int` (`increment_ id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'self-increasing primary key', `stu_ age`student age', `is_ deleted`tinyint unsigned DEFAULT'0' COMMENT'0: not deleted 1: delete', `col1`bigint NOT NULL COMMENT 'bigint field', PRIMARY KEY (`increment_ id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='int test table'; 2. Selection of time type

The time field type can be datetime or timestamp. The following table shows the difference between the two:

Timestamp is translated into Chinese as "timestamp", which is the number of seconds from the current time to the first year of Unix (00:00:00, January 1, 1970), occupies 4 bytes, and is saved in UTC format. It automatically retrieves the current time zone and converts it. Datetime is stored in 8 bytes and does not retrieve the time zone. In other words, for timestamp, if the storage time zone is different from the retrieval time zone, then the data will be different. For datetime, what you save is what you get. Here are some common cases and suggestions for selection.

It is recommended to select datetime according to the storage range, such as production time, shelf life and so on, because datetime can store a wider range.

It is recommended to use timestamp to record the insertion time and modification time of the data in this line.

Timestamp is selected for the time field related to the time zone.

If you just want to represent the year, date, and time, you can also use year, date, and time, which occupy 1, 3, and 3 bytes, respectively, and datetime is their collection.

If the timestamp field is often used for queries, we can also use the built-in functions FROM_UNIXTIME () and UNIX_TIMESTAMP () of MySQL to convert date and timestamp numbers back and forth. After conversion, we can use INT UNSIGNED to store time. The numbers are continuous, take up less space, and indexes can be used to improve query performance. The following is a demonstration table-building statement and timestamp-related transformation SQL:

CREATE TABLE `timetime` (`increment_ id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'self-increment primary key', `col1`time` 2020-10-01 00increment_ COMMENT 'expiration', `unix_ createtime` int unsigned NOT NULL COMMENT 'creation timestamp', `create_ time`timetime` creation time', `update_ time`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'modification time', PRIMARY KEY (`timeid`) KEY `idx_unix_ createtime` (`unix_ createtime`) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='time test table' # insert data insert into tb_time (unix_createtime,create_time) values (UNIX_TIMESTAMP (now ()), now ()); # timestamp digits and time conversion select UNIX_TIMESTAMP ('2020-05-06 00 values') select FROM_UNIXTIME (1588694400) 3. Store IP value

IP values are generally stored using char or varchar, but character types are not very efficient when looking up and counting. MySQL database has built-in two IP-related functions INET_ATON () and INET_NTOA (), which can convert IP addresses and integer types. After conversion, you can use INT UNSIGNED to store IP, and the converted numbers are continuous, which improves the query performance and takes up less space.

CREATE TABLE `tb_ ip` (`IP', PRIMARY KEY id` int (10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'self-increment key', `name` varchar (100) NOT NULL COMMENT 'name', `inet_ ip` int (10) unsigned NOT NULL COMMENT 'IP', PRIMARY KEY (`increment_ id`), KEY `idx_inet_ ip` (`inet_ ip`) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='ip test table' # insert data insert into `name` (`name`, `inet_ ip`) values ('wang',INET_ATON (' 192.168.0.1')), ('lisi',INET_ATON (' 192.168.0.2')); # convert select INET_ATON ('192.168.0.1'); select INET_NTOA (3232235521). After reading the above, have you mastered the skills of MySQL database table design? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.