A preface

The data type of each database is never a simple data structure, especially the use of different databases, different engines, the support of the data type is not the same, choose that data type as the field type of the database performance is also very different, so there is a comprehensive understanding of the data type, It is especially important to choose the right data type when designing the database table. This is based on mysql InnoDB data type knowledge, to learn mainstream knowledge;

Learning the basis of this article is the knowledge seeker hair SQL series articles (public number readers directly in the column can be found)

SQL- Do you Really Know anything about SQL?

“SQL- Xiaobai best start SQL query 1”

“SQL- The best introduction to SQL query 2”

SQL: Insert, Update and Delete

SQL-SQL transaction Manipulation

Public account: Knowledge seeker

Inheriting the spirit of open Source, Spreading technology knowledge;

Basic data types

2.1 String Types

  • char(n)Type, a fixed-length string, that is, each value stored occupies the same amount of space;Suitable for storing fields of the same length, such as telephone number, ID number, password, etc; Mysql will automatically delete the space reserved at the end of the char field when storing the char field. If it is SQL SERVER, whitespace characters are left behind; It stores 0-255 characters at most, that is, 8 bits;
  • varchar(n)A variable length string, such as varchar(n), which can store n bytes; If the string length is less than or equal to 255 bytes, n bytes are used to store data, and an additional byte is used to record the N value, such as vARCHar (200), which is actually vARCHar (201). If the string length is greater than 255 bytes, the n value is recorded in two bytes, such as vARCHar (500), which is varchar(502). Its maximum storage 0-65535 bytes, that is, 64 bits; Due to its variable length, the performance overhead is relatively high during update.
  • TEXTThe long text type stores large data and has certain character sets and sorting rules. Therefore, if the text bit is set, the performance deteriorates and occupies large disk space. The length ranges from 0 to 65535 bytes. There are also derived data typesTINYTEXT(0-255 bytes),MEDIUMTEXT(0-16 777 215 bytes),LONGTEXT0-4 294 967 295 bytes);
  • blogType that stores a random binary string, which stores (0-65 535) bytes; The derivative types areTINYBLOB(0-255 bytes),MEDIUMBLOB(0-16 777 215 bytes),LONGBLOB(0-4 294 967 295 bytes);

The actual development of the general will choose char, VARCHAR type for data storage, text used less, blog caution, pit relatively large; And specify different data type length also directly affects the performance of the database, each time with the disk I/O capacity is not the same;

2.2 the integer

  • Tinyint 1 byte 8 bits
  • Smallint 2 bytes 16 bits
  • Mediumint 3 bytes 24 bits
  • Int 4 bytes 32 bits
  • Bigint 8 bytes 64 bits

Its storage size is -2^ (n-1) to 2^ (n-1) -1, where N is a bit, if it is an unsigned integer, then from 0, it seems that there is no negative number, but its actual storage situation and signed integer performance is similar, in the actual development should be based on different business needs, choose the appropriate type;

Tinyint (1) is used to indicate Boolean type, 1 is true, 0 is false;

2.3 floating-point

  • Float (single precision) 4 bytes
  • Double 8 bytes
  • Decimal, which can specify the storage precision, decimal(M,D), where M represents the total number of digits,D represents the decimal place, and m-d represents the integer bit; For example, it is recommended to use Decimal for money-related calculations, otherwise it causes a loss of accuracy; go home and wait for a notice when you go to an interview and ask to use float.

2.4 Date and time types

  • Date, usually in the YYYY-MM-DD format. It can also be formatted in other formats, all of which are beyond the scope of this article.
  • Time: the time format is hh:mm:ss.
  • Year the year YYYY; The use of YY is not recommended
  • Datetime, the date and time format, the date range is 1001 to 9999 years, accuracy is seconds; Occupy 8 bits;
  • Timestamp, which is the same as a Unix timestamp, is stored up to 2038 from midnight on January 1, 1970. It depends on the time zone and occupies 4 bits.

In daily development, it is recommended to use TIMESTAMP to store time, which has better performance than datetime. Their standard format is YY-MM-DD hh: MM: SS;

2.5 Enumeration and SET Types

  • enum(val1, val2, val3…) , used to store fixed values, such as gender male/female, spring, summer, autumn and winter; Its internal use integer sort, display using string, so when sorting may occur some strange phenomenon, can be usedfieldPerform a specified sort; Storage size is 16 bits;

The sample

create table test_enum ( `gender` enum('male'.'woman'));INSERT INTO `zszxz`.`test_enum`(`gender`) VALUES ('male');
Copy the code
  • set(val1, val2, val3…) A set, similar to an array, can store multiple values rather than enumerations, which can store only a single value. The storage size is 64 bits;

The sample

create table test_set ( `gender` set('male'.'woman'));INSERT INTO `zszxz`.`test_set`(`gender`) VALUES ('Male, female');
Copy the code

2.6 other

Other data types, such as bits, store bits, have big pits; Not recommended; Integer is preferred among many data types. Its performance is much faster than string collation. Field modifiers usually include NULL, NOT NULL, and CONMENT. If a non-empty field is NOT NULL by default, it can improve performance and is relatively simple to use indexes. Try not to use foreign keys because each foreign key incurs additional performance overhead;

Focus on knowledge seekers: