How to Learn the data types and table field constraint types supported by MySQL,

How to Learn the data types and table field constraint types supported by MySQL,

MySQL Data (field) Type
When creating a table, you must clearly define the data type corresponding to the field. MySQL data types include numeric, string (text), time and date, and other data types.
Value Type
Value Type description:

Additional instructions
In the int (integer) series, only integer values can be stored, and the displayed size (M) can be specified with parentheses. If not specified, the size is allocated by default. If the display width of the actual value is greater than the set value, the actual value is displayed instead of truncated to fit the display size. For example, 3 in smallint (3) is the display size, that is, the three-digit value (excluding the-Number) is displayed)
Int type can specify the UNSIGNED attribute, that is, the UNSIGNED (non-negative), so there are two storage scopes
In float, double, and decimal types, the UNSIGNED attribute cannot be specified. Its display size contains the decimal precision (D), that is, the float (3, 1) Storage range is-99.9 to 99.9.
Decimal must specify the display size (M) and decimal precision (D). float and double are optional.
Select a smaller type to improve efficiency and save storage space. For example, select tinyint (3) for age ). This principle applies to character types
String (text) Type
String (text) type description:

Additional instructions
The length must be specified for char and varcha. The difference is that char is always stored according to the specified length, while varchar adds a byte to allocate space based on the actual string length.
Time and date type
Description of time and date types:

Prompt
In PHP, the time is usually stored in the table in the int type according to the UNIX timestamp, and then processed by the PHP time function based on actual needs, but not all of them are like this.

MySQL table field attributes
Primary Key
The primary key (primary key) of a table is one or more fields in the Table. Its value uniquely identifies a record in the table. A table cannot have multiple primary keywords, and columns of primary keywords cannot contain null values and duplicate values. The primary keyword is optional and can be defined in the create table or alter table statement.
Syntax:
Primary key (column_name)

In this table, the uid field is the primary key of the table.
General principles for primary key Creation
Primary keys should be meaningless to users.
Never update the primary key. If the primary key needs to be updated, it indicates that the primary key should be meaningless to the user.
The primary key should not contain dynamically changed data, such as the timestamp and creation time.
The primary key should be automatically generated by the system.
Foreign key
In the relationship between two tables, when the primary keyword of A table (such as table A) is included in another table (such as table B, the primary keyword in Table A becomes the foreign key (Foreign keyword) in table B ). Table B is called the master table, and table A is called the slave table.
Foreign keys are mainly used to maintain data consistency and integrity, avoid redundant data, and associate two or more tables.

In this article, the comment table comment sample, uid is the foreign key, which is the primary key of the user table. This table records users' comments on the article. for user information, you only need to correspond to the primary key uid of the user table. In terms of data consistency, for example, when the comment table does not have a comment with uid 3, you can delete the records with uid = 3 in the user table.
Auto Increment
In the MySQL database, the AUTO_INCREMENT attribute is provided to set the field to the int type. Each time a record is added to the data table, the value of this field is automatically added with 1. After auto increment is set, the column does not need to be set with default values and uniqueness constraints.
Example:

uid mediumint(8) NOT NULL auto_increment

Not empty
Due to some logic requirements, it is sometimes necessary to set the field attribute to a non-NULL (not null) field, such as a non-NULL field such as a record user name or password.
It is not required to set a non-empty column, but it is best to set a default value to avoid unexpected errors and reduce the SQL statement complexity when increasing data. When you add a data record to a data table, if you do not set a non-empty field to write data, the system will write data by default.
Example:

Username char (20) not null default ''// change not null to nullalter table user CHANGE username char (20) NULL

Prompt
In the preceding example, CHANGE username indicates the name of the changed field, and CHANGE username indicates the new field name. In this example, only the field attribute is changed to NULL, and the field name is not changed. Therefore, the two are consistent.
Null
In contrast to non-empty fields, you can set the field attribute to NULL. If the field attribute is NOT set to a non-empty (not null) attribute, the system default value can be NULL.
Change NULL to not null:

ALTER TABLE user CHANGE username username char(20) NOT NULL DEFAULT ''

Uniqueness Constraint
Sometimes some field data cannot be repeated, such as the user name. In this case, you need to add a UNIQUE constraint (UNIQUE ).
Syntax:

UNIQUE (column_name) // add uniquealter table tb_name add unique (column_name) to the TABLE field // remove uniquealter table tb_name drop index column_name

A primary key is mandatory and has automatically defined uniqueness constraints, so you do not need to define UNIQUE.
Index
Index is an identifier attached to a field to increase the query speed. When we create an appropriate index for some fields that may need to be frequently queried, the query speed will be significantly improved when querying the field data.
Syntax:
KEY key_name (column_name)
The character after the KEY is the index name, and the field name that creates the index in brackets.
Improved table creation SQL

CREATE TABLE user (  uid mediumint(8) unsigned NOT NULL auto_increment,  username char(15) NOT NULL default '',  password char(32) NOT NULL default '',  email varchar(40) NOT NULL default '',  regdate int(10) unsigned NOT NULL default '0',  PRIMARY KEY (uid),  UNIQUE KEY username (username),  KEY email (email)) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Additional instructions
Several other attributes of table creation are attached to the statement body:
ENGINE: indicates the storage ENGINE type, which can be MyISAM or InnoDB. MyISAM does not support advanced processing such as transaction processing. It emphasizes the performance of tables and performs faster than InnoDB. While InnoDB provides transaction support for advanced database functions such as external keys, which has poor performance than MyISAM. The default value is MyISAM.
CHARSET: indicates the character set of the data table. It is generally gbk, utf8, and big5. For compatibility consideration, we set it to the utf8 character set.
AUTO_INCREMENT: Set the default growth start Number of the primary key.

Articles you may be interested in:

• MySQL date data type and time type usage Summary
• MySQL study NOTE 4: integrity constraints Field