MySQL Column property
The primary key of a table is one or more fields in the table whose value uniquely identifies a record in the table. A table can not have multiple primary keys, and a primary key column can not contain null and duplicate values. The primary key is optional and can be defined in the CREATE TABLE or ALTER TABLE statement.
PRIMARY KEY (column_name)
In this table,
id this field is the primary key of the table.
- The primary key should be meaningless to the user
- Never update the primary key, if the primary key needs to be updated, then the principle that the primary key should be meaningless to the user is violated
- The primary key should not contain dynamically changing data such as timestamps, creation times, and so on
- The primary key should be automatically generated by the system
In the relationship between the two tables, when a table (such as Table A) is the main keyword is included in another table (such as Table B), the main table in the A table will become the B key table (Foreign keywords). B table as the main table, A table is called from the table.
Foreign key is mainly used to maintain data consistency, integrity, to avoid redundant data, so that two or more forms form association.
|1||1||Comments Test. .||1278011380||126.96.36.199|
|2||1||Write a very detailed way, thank you! "||1278063917||188.8.131.52|
In this article comment table comment example, uid is a foreign key, it is the user table primary key. In the table records the user's comments on the article, on user information, only the user table corresponding to the primary key uid can. In the data consistency, for example, when the comment table does not uid 3 comments, you can delete the user table uid = 3 records.
In the MySQL database, the field is automatically incremented (AUTO_INCREMENT) attribute, that is, the field is set to int class data type, each add a record to the data table, the value of the field will be automatically increased by one. With autoincrement set, this column eliminates the need to set default and unique constraints. example:
id mediumint (8) NOT NULL auto_increment
Because of some logical requirements, and sometimes need to set the field attribute is not empty (NOT NULL), such as the record user name, password and other non-null value field.
Set to a not null column, although it is not required, but it is best to set a default value to prevent unexpected errors and reduce the complexity of SQL statements when adding data. When adding data records to the data table, if the field is set to non-empty data is not written, the system will be the default value to write.
username char (20) NOT NULL default '' // Change NOT NULL to NULL ALTER TABLE user CHANGE username username char (20) NULL
In contrast to not null, you can set the field property to null. If the field property is not set to the NOT NULL attribute, the system defaults to NULL.
Change NULL to NOT NULL:
ALTER TABLE user CHANGE username username char (20) NOT NULL DEFAULT ''
Sometimes some field data is not allowed to repeat, such as user name, this time on the need to increase the uniqueness constraint (UNIQUE).
UNIQUE (column_name) // Add UNIQUE to table fields ALTER TABLE tb_name ADD UNIQUE (column_name) // Remove UNIQUE ALTER TABLE tb_name DROP INDEX column_name
The primary key will be mandatory to have the automatic definition of the unique constraints, without additional definition of UNIQUE.
A database index is an identifier that is appended to a field to increase the speed of a query. We may need to table some of the frequently asked field to establish the appropriate index, then query the field data, it will significantly speed up the query speed.
KEY index_name (column_name)
CREATE TABLE user ( id 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 '', PRIMARY KEY (id), UNIQUE KEY username (username), KEY email (email) ) ENGINE = MyISAM DEFAULT CHARSET = utf8 AUTO_INCREMENT = 1;Explaination
- ENGINE: that storage engine type, divided into MyISAM and InnoDB two types.
MyISAM does not support transaction processing and other advanced processing, emphasizing the performance of the table, the implementation is also faster than InnoDB.
While InnoDB provides transaction support has been external keys and other advanced database features, the performance difference than MyISAM. The default is MyISAM
- CHARSET: table that the data table character set, usually gbk or utf8 and big5, etc., for compatibility considerations, we set the utf8 character set
- AUTO_INCREMENT: Set the default number of primary key growth