MySQL

MySQL Column property


Primary key

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)
id username password email
1 admin b7e591c246d010bb2ccd77d52490c85e admin@gmail.com
2 John a193686a53e4de85ee3f2ff0576adf01 john@gmail.com
3 Jack 0193686a35e4de85ee3f2ff0567adf490 jack@gmail.com

In this table, id this field is the primary key of the table.

The general principle of establishing a primary key should be followed
  • 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

Foreign key

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.

id uid comment createtime clientip
1 1 Comments Test. . 1278011380 123.123.123.123
2 1 Write a very detailed way, thank you! " 1278063917 123.123.123.123
3 3 Learning! 1278069380 123.123.123.123

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.


Auto Increment

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

not null

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.

Example

username char (20) NOT NULL default ''
// Change NOT NULL to NULL
ALTER TABLE user CHANGE username username char (20) NULL

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 ''

Uniqueness constraint

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.


index

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)

Complete Example

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
  1. 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

  2. CHARSET: table that the data table character set, usually gbk or utf8 and big5, etc., for compatibility considerations, we set the utf8 character set
  3. AUTO_INCREMENT: Set the default number of primary key growth



Copyright 2014-2017 by DeveloperTut.com. All Rights Reserved.
TwitterGoogle+Facebook