This article describes the MySQL data types and field attributes. Share with you for your reference, the details are as follows:

In this article:

• Data Types
  • Numeric Types
    • Integer
    • Floating point
    • Fixed-point type
  • Date and time types
  • String Type
  • Replenish:
  • • Display width and zerofll
    • Record length
• Field properties
  • Empty\Not empty value: NULL, NOT NULL
  • primary key
  • unique key: unique key
  • Self-growth: auto_increment
  • Default value: default
  • Field Description: comment
  • Replenish:
  • • Composite Key

Release date: 2018-04-08

Data Type:

The data type of MySQL is the type of stored data.

Numeric Type:

• Integer types: tinyint, smallint, mediumint, integer, bigint
  • The default is signed. If you need to use an unsigned data type, add "UNSIGNED" at the end.

• Decimal Type:
  • Floating point type: float(size,d), double(size,d)
    • size is the maximum number of digits in the value, and d is the number of digits to the right of the decimal point (even if you don't enter that many digits, they will be filled in).
    • The precision of FLOAT is 6 to 7 digits (depending on the situation? After all, the machine is stored in binary), and the precision of DOUBLE is 14 to 15 digits.
    • The number of integer parts is SIZE-D. The number of values ​​stored directly cannot be more than this. However, if the floating point number is rounded off and the integer overflows and exceeds the maximum number of digits, the system allows it (some versions seem to no longer allow this, so generally the number of decimal places should not exceed this).
    • Many times, you don't need to pay attention to the value range of two floating points (which is extremely large), but you should pay attention to its precision.
    • 
  • Fixed-point type: decimal(size,d)
    • size is the maximum number of digits in a value, which can be up to 65 , and d is the maximum number of digits to the right of the decimal point, which can be up to 30 .
    • Fixed-point types do not allow the integer part to exceed the range due to rounding of the decimal part.
    • DECIMAL does not lose precision. It can be understood as using "string" storage (the MYSQL authoritative guide says so).
    • 
  • The data types float, double, and decimal without brackets represent no decimal part.

Date and time type:

Date and time types include date, time, datetime, timestamp, year;

• Datetime: time and date, the format is YYYY-MM-DD HH:II:SS, the range is from 1000 to 9999 (some versions already allow 0-9999? Not sure);
  • 

• Date: Date, the format is YYYY-MM-DD, the range is from 1000 to 9999
  • 
• Time: time period, the format is HH:II:SS, the specified time is between a certain interval, it can be positive or negative. The range of -838:59:59~838:59:59, which represents the time range before and after a certain date [if you only want to use it to represent the time within 24 hours, you can ignore its time range]
  • 
• Timestamp: The format is YYYY-MM-DD HH:II:SS, which ranges from 1970-01-01 00:00:00 to 2038-01-19 03:14:07. [Once the row record is modified, the timestamp will be changed to the current time. 】
  • 
• Year: Year, the range is 1901-2156
• • 
  • Originally there were two formats, year(2) and year(4), but year(2) was removed in 5.7.

String type:

• set : set string. Put specific strings into the collection.

  • After a set is defined, you cannot insert strings outside the set elements (you can insert multiple strings that meet the conditions)
  • In fact, the collection string only stores numerical values, which are automatically converted into corresponding strings by the system.
  • Each element in the set corresponds to a binary bit. The selected one is 1, and the unselected one is 0. The left side is the low bit in the binary, and the right side is the high bit. The binary result is obtained from right to left.

create table my_set(
hobby set('football','basketball','pingpong','computer')
)charset utf8;

insert into my_set values('basketball,computer');
insert into my_set values(3);-- 3=0011
insert into my_set values(5);-- 5=0101 

• enum : enumeration string

  • It is a bit like set, but only one string can be inserted here (the value of a field of set type can be multiple set elements, but enum can only be one of them)
  • List the possible data, and the data actually stored can only be the listed data.
  • Using enumerations helps unify data and save storage space (enumerations only store numerical values, which are converted into strings by the system).
  • The enumeration elements are actually numbered in the order they are defined, starting at 1, so you can directly insert the numeric value corresponding to the enumeration element.
  • like:

create table my_enum(
gender enum('male','female','unknown')
)charset utf8; 

• blob : binary data string

  • Store binary data (rarely used)

• text : text string

  • To store text, when the number of characters is greater than 255, text is generally used;

• varchar: variable length string

  • varchar(L), L represents the character length, the unit is character, and the maximum length is 65536. In actual storage, 1 to 2 bytes are required to store the length (1 or 2 is determined by the actual size, 2 ¹⁶ =35536, generally one byte if <= 255). When used, space is allocated according to the defined size, but the space will be reduced according to actual needs (for example, if the specified size is 60, but the actual size is only 30, then the actual space is 31 to 32).
  • When L is greater than 255, it is recommended to use Text instead of varchar;
  • 

• char: fixed-length string

  • char(L), L represents the character length, the unit is character, the maximum length is 255, and the default is 1 when L is not used
  • The actual space occupied by characters in different environments varies according to the character size. In the UTF8 environment, the number of bytes used by char(4) is 4*3=12.

Replenish:

• Display Width and Padding:

  • The value displayed after the column type represents the minimum number of digits (including the sign) that the data will eventually be displayed.
  • Generally, data that is not large enough to display will be padded with spaces. You can also use zerofill to fill it with zeros (zero filling will cause the data type to automatically become unsigned).
  • Tiny(3) space padding effect: , zerofill effect:

create table myInt(t1 TINYINT(3));
drop table myInt;
insert into myInt values(127),(1);
select * from myInt;
alter table myInt modify t1 tinyint(3) zerofill;
select * from myInt;

• Don't use the maximum size to store strings easily, because of record length issues:

  • MySQL stipulates that any data cannot exceed 65535 bytes.
  • If any field allows null, the system will automatically save one byte from the entire record to store NULL;
  • Text does not occupy the record length and is stored additionally.
  • The maximum limit of varchar in UTF8 is: 65535-2 (requires two bytes to store the length) / 3 = 21844, so the maximum number of characters is 21844
  • The maximum limit of varchar in GBK: 65535-2 (two bytes are required to store the length) /2=32766

Field properties:

Field attributes are the attributes of a field other than the data type, generally including empty/not empty value, primary key, unique key, auto-increment, default value, description and other attributes.

Empty\Not empty value: NULL, NOT NULL

• By default, the data in a field is allowed to be empty. For example, a person's information record can have no email address (some people may consider using "empty string" to represent it). We allow fields that do not need to be filled in to be set to null; but for example, if we do not want to fill in some required data and leave it blank, we can set this field to not null.
• Empty data is allowed, and empty data is displayed as null:
• When inserting null into a non-empty field, an error will be reported:
• How to define null\not null attributes (because null is allowed by default, we mainly focus on not null): ; Of course, you can also define the not null attribute for the field by modifying the field format

primary key

• The primary key is used to uniquely identify each record (everyone has their own unique ID card)
• Each table can have only one primary key.
• Because it is a unique identifier, the data in the primary key field cannot be empty, and the data value of the primary key field cannot be repeated.
• The primary key is also an index that can increase the search speed.
• Definition of primary key:
  • 1. Define it as the attributes of the field when defining the table structure.
  • 2. Modify the table structure and add a field with a primary key.
  • 3. Modify the table structure and add a primary key attribute to a field.
• Purpose of primary key: The main purpose of primary key is to uniquely identify each record. For example, if we want to encapsulate the many-to-many relationship between two tables (student table and teacher table) into one table, the extracted mapping fields are generally their respective primary keys. ; and because the primary key data is non-repeatable, it is also used to constrain the uniqueness of the data.
• Deleting the primary key:

  alter table table name drop primary key;

unique key: unique key

• The function of a unique key is similar to that of a primary key, but the difference is that there can be only one primary key, while there can be multiple unique keys, and the data in the field of the unique key is allowed to be empty.
• The unique key can constrain the field so that the data in the field cannot be repeated.
• If the unique key also has not null and there is no primary key in the table, it will be displayed as the primary key in the desc table structure.
• If the unique key does not allow nulls, then the function is the same as the primary key.
• The definition method of the unique key can refer to the primary key.
• Deletion of unique key:

  alter table table name drop index unique key name; -- The unique key uses the field name to define the name by default.

Self-growth: auto_increment

• The function of auto-increment is to make the data of a field grow as records are inserted (without inserting data into this field)
• The premise of self-increment is that this field must be an "index", such as a primary key or a unique key.
• The premise of self-increment is that the data type of this field is a numeric type. (If float is given, it will not grow into a decimal, but only an integer)
• A table can have only one auto-increment.
• Only when no value is given or null is given can the field be automatically increased correctly; if a certain self-increment fails, the field will continue to be automatically increased from the maximum value of the current field next time.
• How to define auto-growth: ; Of course, you can also define the not null attribute for the field by modifying the field format
• Modifications to auto-growth:
  • Modify the growth rate: set auto_increment = value;
  • Modify the value of the next increase: alter table table name auto_increment = value; [The modified value can only be increased, not decreased, because it may cause data duplication problems]
• View auto-increment: show vairable like "auto_increment%";
• Deletion of self-increment: Use alter to delete. When alter modifies the field attributes, if the original attributes are not included, they will be deleted:

Default value: default

• The function of the default value is to use the default value as the data when we do not assign a value to a field. For example, when gender is not selected, "confidential" is used by default.
• The default value is defined as:
• 

Field Description: comment

• The field description is used to describe the field. It can be displayed when viewing the data table creation statement (it will not be displayed in the select result), which can help us understand the meaning of a certain field.
• Definition:
• 

Replenish:

• Composite key: Multiple fields can be combined into one key. For example, a student can take multiple courses, but the student ID and course ID should be unique when combined. Here, the primary key is used to limit this situation.
  • Definition method (composite takes primary key as an example):
  • Data constraints (taking the above table as an example):

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.