{"id":1910,"date":"2019-04-24T14:59:22","date_gmt":"2019-04-24T06:59:22","guid":{"rendered":"https:\/\/www.yusian.com\/blog\/?p=1910"},"modified":"2020-12-02T15:02:04","modified_gmt":"2020-12-02T07:02:04","slug":"%e7%b4%a2%e5%bc%95","status":"publish","type":"post","link":"https:\/\/www.yusian.com\/blog\/database\/2019\/04\/24\/1459221910.html","title":{"rendered":"\u7d22\u5f15"},"content":{"rendered":"<h3>0\u3001\u7d22\u5f15\u7c7b\u578b<\/h3>\n<ul>\n<li>\u666e\u901a\u7d22\u5f15\uff1aindex\u3001key<\/li>\n<li>\u552f\u4e00\u7d22\u5f15\uff1auniqe<\/li>\n<li>\u4e3b\u952e\u7d22\u5f15\uff1aprimary key<\/li>\n<li>\u5168\u6587\u7d22\u5f15\uff1afulltext\uff08MyISAM\u5f15\u64ce\u6216InnoDB\u5f15\u64cev5.7\u53ca\u4ee5\u4e0a\u7248\u672c\u652f\u6301\uff09<\/li>\n<\/ul>\n<h3>1\u3001\u67e5\u770b\u5f53\u524d\u8868\u521b\u5efa<\/h3>\n<ul>\n<li>show create table user;<\/li>\n<\/ul>\n<pre><code class=\"language-sql line-numbers\">mysql&gt; show create table user;\n+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\n| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                        |\n+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\n| user  | CREATE TABLE `user` (\n  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n  `user_name` varchar(20) NOT NULL,\n  `password` char(32) NOT NULL,\n  `email` varchar(50) NOT NULL,\n  `age` tinyint(3) unsigned NOT NULL,\n  `user_des` varchar(255) DEFAULT NULL,\n  PRIMARY KEY (`id`),\n  UNIQUE KEY `user_name` (`user_name`),\n  KEY `age` (`age`),\n  KEY `email` (`email`),\n  FULLTEXT KEY `user_des` (`user_des`)\n) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |\n+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\n1 row in set (0.00 sec)\n<\/code><\/pre>\n<h3>2\u3001\u67e5\u770b\u5f53\u524d\u8868\u7d22\u5f15<\/h3>\n<ul>\n<li>show index from user<\/li>\n<\/ul>\n<pre><code class=\"language-sql line-numbers\">mysql&gt; show index from user\\G\n*************************** 1. row ***************************\n        Table: user\n   Non_unique: 0\n     Key_name: PRIMARY\n Seq_in_index: 1\n  Column_name: id\n    Collation: A\n  Cardinality: 1\n     Sub_part: NULL\n       Packed: NULL\n         Null:\n   Index_type: BTREE\n      Comment:\nIndex_comment:\n*************************** 2. row ***************************\n        Table: user\n   Non_unique: 0\n     Key_name: user_name\n Seq_in_index: 1\n  Column_name: user_name\n    Collation: A\n  Cardinality: 1\n     Sub_part: NULL\n       Packed: NULL\n         Null:\n   Index_type: BTREE\n      Comment:\nIndex_comment:\n*************************** 3. row ***************************\n        Table: user\n   Non_unique: 1\n     Key_name: age\n Seq_in_index: 1\n  Column_name: age\n    Collation: A\n  Cardinality: 1\n     Sub_part: NULL\n       Packed: NULL\n         Null:\n   Index_type: BTREE\n      Comment:\nIndex_comment:\n*************************** 4. row ***************************\n        Table: user\n   Non_unique: 1\n     Key_name: email\n Seq_in_index: 1\n  Column_name: email\n    Collation: A\n  Cardinality: 1\n     Sub_part: NULL\n       Packed: NULL\n         Null:\n   Index_type: BTREE\n      Comment:\nIndex_comment:\n*************************** 5. row ***************************\n        Table: user\n   Non_unique: 1\n     Key_name: user_des\n Seq_in_index: 1\n  Column_name: user_des\n    Collation: NULL\n  Cardinality: 1\n     Sub_part: NULL\n       Packed: NULL\n         Null: YES\n   Index_type: FULLTEXT\n      Comment:\nIndex_comment:\n5 rows in set (0.00 sec)\n<\/code><\/pre>\n<h3>3\u3001\u5168\u6587\u7d22\u5f15\u67e5\u8be2<\/h3>\n<ul>\n<li>select * from user where match(xxxx) against(\u2018xxxx\u2019);<\/li>\n<\/ul>\n<pre><code class=\"language-sql line-numbers\">mysql&gt; select * from user where match(user_des) against('abc');\n+----+-----------+----------------------------------+----------------+-----+----------+\n| id | user_name | password                         | email          | age | user_des |\n+----+-----------+----------------------------------+----------------+-----+----------+\n|  1 | sian      | c4ca4238a0b923820dcc509a6f75849b | yusian@163.com |  32 | abc def  |\n+----+-----------+----------------------------------+----------------+-----+----------+\n1 row in set (0.06 sec)\n<\/code><\/pre>\n<h3>4\u3001\u5916\u952e\u7ea6\u675f\uff08MyISAM\u4e0d\u652f\u6301\uff09<\/h3>\n<ul>\n<li>alter table mark add foreign(user_id) references user(id)\uff1b<\/li>\n<li>\u6709\u7ea6\u675f\u76f8\u5173\u7684\u5b57\u6bb5\u6216\u8868\u4e0d\u80fd\u5220\u9664\uff1b<\/li>\n<\/ul>\n<pre><code class=\"language-sql line-numbers\">mysql&gt; select * from user;\n+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+\n| id | user_name | sex | password                         | email           | address | fee     | age | create_at           |\n+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+\n|  1 | sian      | \u7537  | e10adc3949ba59abbe56e057f20f883e | yusian@163.com  | NULL    | 1200.00 |  32 | 2019-04-23 15:57:29 |\n|  2 | \u4f59\u897f\u5b89    | \u7537  | c4ca4238a0b923820dcc509a6f75849b | yusian@163.com  | NULL    | 1002.00 |  33 | 2019-04-23 21:00:03 |\n|  3 | Jack      | \u7537  | c81e728d9d4c2f636f067f89cc14862c | jack@yusian.com | NULL    |    0.00 |  22 | 2019-04-21 20:11:59 |\n|  4 | Rose      | \u5973  | eccbc87e4b5ce2fe28308fd9f2a7baf3 | rose@yusian.com | NULL    |    0.00 |  18 | 2019-04-22 10:46:41 |\n|  5 | Sim       | \u5973  | a87ff679a2f3e71d9181a67b7542122c | sim@yusian.com  | NULL    |    0.00 |  28 | 2019-04-22 10:46:41 |\n|  6 | Tony      | \u7537  | c4ca4238a0b923820dcc509a6f75849b | tony@yusian.com | NULL    |    0.00 |  22 | 2019-04-21 20:41:26 |\n|  7 | lucy      | \u7537  | c81e728d9d4c2f636f067f89cc14862c | lucy@163.com    | NULL    |    0.00 |   0 | 2019-04-23 20:13:01 |\n+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+\n7 rows in set (0.00 sec)\n\n\nmysql&gt; select * from mark;\n+----+------+---------+\n| id | mark | user_id |\n+----+------+---------+\n|  1 |   90 |       1 |\n|  2 |   88 |       2 |\n|  4 |   92 |       4 |\n|  5 |  100 |       5 |\n|  6 |   77 |       6 |\n|  7 |   99 |       7 |\n| 11 |   44 |       3 |\n+----+------+---------+\n7 rows in set (0.00 sec)\n\nmysql&gt; insert into mark(mark, user_id) values(99, 8);\nERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`sian`.`mark`, CONSTRAINT `mark_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`))\n\nmysql&gt; delete from user where id = 7;\nERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`sian`.`mark`, CONSTRAINT `mark_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`))\nmysql&gt; \n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>0\u3001\u7d22\u5f15\u7c7b\u578b \u666e\u901a\u7d22\u5f15\uff1aindex\u3001key \u552f\u4e00\u7d22\u5f15\uff1auniqe \u4e3b\u952e\u7d22\u5f15\uff1aprimary key \u5168\u6587\u7d22\u5f15\uff1afulltext\uff08MyISAM\u5f15\u64ce\u6216InnoDB\u5f15\u64cev5.7\u53ca\u4ee5\u4e0a\u7248\u672c\u652f\u6301\uff09 1\u3001\u67e5\u770b\u5f53\u524d\u8868\u521b\u5efa show create table user; mysql&gt; show create table user; +&#8212;&#8212;-+&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;+ | Table | Create Table | +&#8212;&#8212;-+&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;+ | user | CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_name` varchar(20) NOT NULL, `password` char(32) NOT NULL, `email` varchar(50) NOT NULL, `age` tinyint(3) unsigned NOT NULL, `user_des` [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[282],"tags":[327,333,22,334],"class_list":["post-1910","post","type-post","status-publish","format-standard","hentry","category-database","tag-database","tag-index","tag-mysql","tag-334"],"_links":{"self":[{"href":"https:\/\/www.yusian.com\/blog\/wp-json\/wp\/v2\/posts\/1910","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.yusian.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.yusian.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.yusian.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.yusian.com\/blog\/wp-json\/wp\/v2\/comments?post=1910"}],"version-history":[{"count":0,"href":"https:\/\/www.yusian.com\/blog\/wp-json\/wp\/v2\/posts\/1910\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.yusian.com\/blog\/wp-json\/wp\/v2\/media?parent=1910"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.yusian.com\/blog\/wp-json\/wp\/v2\/categories?post=1910"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.yusian.com\/blog\/wp-json\/wp\/v2\/tags?post=1910"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}