{"id":11451,"date":"2025-06-12T10:50:24","date_gmt":"2025-06-12T02:50:24","guid":{"rendered":"https:\/\/egonlin.com\/?p=11451"},"modified":"2025-06-12T10:52:39","modified_gmt":"2025-06-12T02:52:39","slug":"mysql%e7%b4%a2%e5%bc%95%e7%ae%a1%e7%90%86","status":"publish","type":"post","link":"https:\/\/egonlin.com\/?p=11451","title":{"rendered":"MySQL\u7d22\u5f15\u7ba1\u7406"},"content":{"rendered":"

\u4e00 \u529f\u80fd<\/strong><\/p>\n

\n
#1. \u7d22\u5f15\u7684\u529f\u80fd\u5c31\u662f\u52a0\u901f\u67e5\u627e\r\n#2. mysql\u4e2d\u7684primary key\uff0cunique\uff0c\u8054\u5408\u552f\u4e00\u4e5f\u90fd\u662f\u7d22\u5f15\uff0c\u8fd9\u4e9b\u7d22\u5f15\u9664\u4e86\u52a0\u901f\u67e5\u627e\u4ee5\u5916\uff0c\u8fd8\u6709\u7ea6\u675f\u7684\u529f\u80fd<\/pre>\n<\/div>\n
\u4e8c MySQL\u5e38\u7528\u7684\u7d22\u5f15\u5206\u7c7b<\/strong><\/p>\n
\u6211\u4eec\u53ef\u4ee5\u5728\u521b\u5efa\u4e0a\u8ff0\u7d22\u5f15\u7684\u65f6\u5019\uff0c\u4e3a\u5176\u6307\u5b9a\u7d22\u5f15\u7c7b\u578b\uff0c\u4e3b\u8981\u6709\u4ee5\u4e0b\u51e0\u7c7b<\/p>\n
\n
#===========B+\u6811\u7d22\u5f15\uff08innodb\u5b58\u50a8\u5f15\u64ce\u9ed8\u8ba4\uff09\r\n\r\n\u805a\u96c6\u7d22\u5f15\uff1a\u5373\u4e3b\u952e\u7d22\u5f15\uff0cPRIMARY KEY\r\n    \u7528\u9014\uff1a\r\n        1\u3001\u52a0\u901f\u67e5\u627e\r\n        2\u3001\u7ea6\u675f\uff08\u4e0d\u4e3a\u7a7a\u3001\u4e0d\u80fd\u91cd\u590d\uff09\r\n\r\n\u552f\u4e00\u7d22\u5f15\uff1aUNIQUE\r\n    \u7528\u9014\uff1a\r\n        1\u3001\u52a0\u901f\u67e5\u627e\r\n        2\u3001\u7ea6\u675f\uff08\u4e0d\u80fd\u91cd\u590d\uff09 \r\n\r\n\u666e\u901a\u7d22\u5f15INDEX\uff1a\r\n    \u7528\u9014\uff1a\r\n        1\u3001\u52a0\u901f\u67e5\u627e\r\n\r\n\u8054\u5408\u7d22\u5f15\uff1a \r\n    PRIMARY KEY(id,name):\u8054\u5408\u4e3b\u952e\u7d22\u5f15 \r\n    UNIQUE(id,name):\u8054\u5408\u552f\u4e00\u7d22\u5f15 \r\n    INDEX(id,name):\u8054\u5408\u666e\u901a\u7d22\u5f15\r\n\r\n#===========HASH\u7d22\u5f15(\u67e5\u8be2\u5355\u6761\u5feb\uff0c\u8303\u56f4\u67e5\u8be2\u6162)\r\n\u5c06\u6570\u636e\u6253\u6563\u518d\u53bb\u67e5\u8be2\r\nInodb\u548cMyisam\u90fd\u4e0d\u652f\u6301\uff0c\u8bbe\u7f6e\u5b8c\u8fd8\u662fBtree\r\nmemery\u5b58\u50a8\u5f15\u64ce\u652f\u6301\r\n\r\n\r\n#===========FULLTEXT\uff1a\u5168\u6587\u7d22\u5f15 \uff08\u53ea\u53ef\u4ee5\u7528\u5728MyISAM\u5f15\u64ce\uff09\r\n\u901a\u8fc7\u5173\u952e\u5b57\u7684\u5339\u914d\u6765\u8fdb\u884c\u67e5\u8be2\uff0c\u7c7b\u4f3c\u4e8elike\u7684\u6a21\u7cca\u5339\u914d\r\nlike + %\u5728\u6587\u672c\u6bd4\u8f83\u5c11\u65f6\u662f\u5408\u9002\u7684\r\n\u4f46\u662f\u5bf9\u4e8e\u5927\u91cf\u7684\u6587\u672c\u6570\u636e\u68c0\u7d22\u4f1a\u975e\u5e38\u7684\u6162\r\n\u5168\u6587\u7d22\u5f15\u5728\u5927\u91cf\u7684\u6570\u636e\u9762\u524d\u80fd\u6bd4like\u5feb\u5f97\u591a\uff0c\u4f46\u662f\u51c6\u786e\u5ea6\u5f88\u4f4e\r\n\u767e\u5ea6\u5728\u641c\u7d22\u6587\u7ae0\u7684\u65f6\u5019\u4f7f\u7528\u7684\u5c31\u662f\u5168\u6587\u7d22\u5f15\uff0c\u4f46\u66f4\u6709\u53ef\u80fd\u662fES\r\n\r\n#===========RTREE\uff1aR\u6811\u7d22\u5f15\r\nRTREE\u5728mysql\u5f88\u5c11\u4f7f\u7528\uff0c\u4ec5\u652f\u6301geometry\u6570\u636e\u7c7b\u578b\r\ngeometry\u6570\u636e\u7c7b\u578b\u4e00\u822c\u586b\u5199\u7ecf\u7eac\u5ea6\u90a3\u6837\u7684\u6570\u636e\r\n\u652f\u6301\u8be5\u7c7b\u578b\u7684\u5b58\u50a8\u5f15\u64ce\u53ea\u6709MyISAM\u3001BDb\u3001InnoDb\u3001NDb\u3001Archive\u51e0\u79cd\u3002\r\nRTREE\u8303\u56f4\u67e5\u627e\u5f88\u5f3a\uff0c\u4f46Btree\u4e5f\u4e0d\u5f31.\r\n\r\n#\u4e0d\u540c\u7684\u5b58\u50a8\u5f15\u64ce\u652f\u6301\u7684\u7d22\u5f15\u7c7b\u578b\u4e5f\u4e0d\u4e00\u6837\r\nInnoDB \u652f\u6301\u4e8b\u52a1\uff0c\u652f\u6301\u884c\u7ea7\u522b\u9501\u5b9a\uff0c\u652f\u6301 B-tree\u3001Full-text \u7b49\u7d22\u5f15\uff0c\u4e0d\u652f\u6301 Hash \u7d22\u5f15\uff1b\r\nMyISAM \u4e0d\u652f\u6301\u4e8b\u52a1\uff0c\u652f\u6301\u8868\u7ea7\u522b\u9501\u5b9a\uff0c\u652f\u6301 B-tree\u3001Full-text \u7b49\u7d22\u5f15\uff0c\u4e0d\u652f\u6301 Hash \u7d22\u5f15\uff1b\r\nMemory \u4e0d\u652f\u6301\u4e8b\u52a1\uff0c\u652f\u6301\u8868\u7ea7\u522b\u9501\u5b9a\uff0c\u652f\u6301 B-tree\u3001Hash \u7b49\u7d22\u5f15\uff0c\u4e0d\u652f\u6301 Full-text \u7d22\u5f15\uff1b\r\nNDB \u652f\u6301\u4e8b\u52a1\uff0c\u652f\u6301\u884c\u7ea7\u522b\u9501\u5b9a\uff0c\u652f\u6301 Hash \u7d22\u5f15\uff0c\u4e0d\u652f\u6301 B-tree\u3001Full-text \u7b49\u7d22\u5f15\uff1b\r\nArchive \u4e0d\u652f\u6301\u4e8b\u52a1\uff0c\u652f\u6301\u8868\u7ea7\u522b\u9501\u5b9a\uff0c\u4e0d\u652f\u6301 B-tree\u3001Hash\u3001Full-text \u7b49\u7d22\u5f15\uff1b\r\n\r\n\r\n<\/pre>\n
\n
\n
\u5404\u4e2a\u7d22\u5f15\u7684\u5e94\u7528\u573a\u666f\uff1a\u4e3e\u4e2a\u4f8b\u5b50\u6765\u8bf4\uff0c\u6bd4\u5982\u4f60\u5728\u4e3a\u67d0\u5546\u573a\u505a\u4e00\u4e2a\u4f1a\u5458\u5361\u7684\u7cfb\u7edf\u3002\r\n\r\n\u8fd9\u4e2a\u7cfb\u7edf\u6709\u4e00\u4e2a\u4f1a\u5458\u8868\r\n\u6709\u4e0b\u5217\u5b57\u6bb5\uff1a\r\n\u4f1a\u5458\u7f16\u53f7 INT\r\n\u4f1a\u5458\u59d3\u540d VARCHAR(10)\r\n\u4f1a\u5458\u8eab\u4efd\u8bc1\u53f7\u7801 VARCHAR(18)\r\n\u4f1a\u5458\u7535\u8bdd VARCHAR(10)\r\n\u4f1a\u5458\u4f4f\u5740 VARCHAR(50)\r\n\u4f1a\u5458\u5907\u6ce8\u4fe1\u606f TEXT\r\n\r\n\u90a3\u4e48\u8fd9\u4e2a \u4f1a\u5458\u7f16\u53f7\uff0c\u4f5c\u4e3a\u4e3b\u952e\uff0c\u4f7f\u7528 PRIMARY\r\n\u4f1a\u5458\u59d3\u540d \u5982\u679c\u8981\u5efa\u7d22\u5f15\u7684\u8bdd\uff0c\u90a3\u4e48\u5c31\u662f\u666e\u901a\u7684 INDEX\r\n\u4f1a\u5458\u8eab\u4efd\u8bc1\u53f7\u7801 \u5982\u679c\u8981\u5efa\u7d22\u5f15\u7684\u8bdd\uff0c\u90a3\u4e48\u53ef\u4ee5\u9009\u62e9 UNIQUE \uff08\u552f\u4e00\u7684\uff0c\u4e0d\u5141\u8bb8\u91cd\u590d\uff09\r\n\r\n#\u9664\u6b64\u4e4b\u5916\u8fd8\u6709\u5168\u6587\u7d22\u5f15\uff0c\u5373FULLTEXT\r\n\u4f1a\u5458\u5907\u6ce8\u4fe1\u606f \uff0c \u5982\u679c\u9700\u8981\u5efa\u7d22\u5f15\u7684\u8bdd\uff0c\u53ef\u4ee5\u9009\u62e9\u5168\u6587\u641c\u7d22\u3002\r\n\u7528\u4e8e\u641c\u7d22\u5f88\u957f\u4e00\u7bc7\u6587\u7ae0\u7684\u65f6\u5019\uff0c\u6548\u679c\u6700\u597d\u3002\r\n\u7528\u5728\u6bd4\u8f83\u77ed\u7684\u6587\u672c\uff0c\u5982\u679c\u5c31\u4e00\u4e24\u884c\u5b57\u7684\uff0c\u666e\u901a\u7684 INDEX \u4e5f\u53ef\u4ee5\u3002\r\n\u4f46\u5176\u5b9e\u5bf9\u4e8e\u5168\u6587\u641c\u7d22\uff0c\u6211\u4eec\u5e76\u4e0d\u4f1a\u4f7f\u7528MySQL\u81ea\u5e26\u7684\u8be5\u7d22\u5f15\uff0c\u800c\u662f\u4f1a\u9009\u62e9\u7b2c\u4e09\u65b9\u8f6f\u4ef6\u5982Sphinx\uff0c\u4e13\u95e8\u6765\u505a\u5168\u6587\u641c\u7d22\u3002\r\n\r\n#\u5176\u4ed6\u7684\u5982\u7a7a\u95f4\u7d22\u5f15SPATIAL\uff0c\u4e86\u89e3\u5373\u53ef\uff0c\u51e0\u4e4e\u4e0d\u7528<\/pre>\n<\/div>\n<\/div>\n
\u4e09 \u521b\u5efa\/\u5220\u9664\u7d22\u5f15\u7684\u8bed\u6cd5<\/strong><\/p>\n
\n
#\u65b9\u6cd5\u4e00\uff1a\u521b\u5efa\u8868\u65f6\r\n    \u3000\u3000CREATE TABLE \u8868\u540d (\r\n                \u5b57\u6bb5\u540d1  \u6570\u636e\u7c7b\u578b [\u5b8c\u6574\u6027\u7ea6\u675f\u6761\u4ef6\u2026],\r\n                \u5b57\u6bb5\u540d2  \u6570\u636e\u7c7b\u578b [\u5b8c\u6574\u6027\u7ea6\u675f\u6761\u4ef6\u2026],\r\n                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY\r\n                [\u7d22\u5f15\u540d]  (\u5b57\u6bb5\u540d[(\u957f\u5ea6)]  [ASC |DESC]) \r\n                );\r\n\r\n\r\n#\u65b9\u6cd5\u4e8c\uff1aCREATE\u5728\u5df2\u5b58\u5728\u7684\u8868\u4e0a\u521b\u5efa\u7d22\u5f15\r\n        CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  \u7d22\u5f15\u540d \r\n                     ON \u8868\u540d (\u5b57\u6bb5\u540d[(\u957f\u5ea6)]  [ASC |DESC]) ;\r\n\r\n\r\n#\u65b9\u6cd5\u4e09\uff1aALTER TABLE\u5728\u5df2\u5b58\u5728\u7684\u8868\u4e0a\u521b\u5efa\u7d22\u5f15\r\n        ALTER TABLE \u8868\u540d ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX\r\n                             \u7d22\u5f15\u540d (\u5b57\u6bb5\u540d[(\u957f\u5ea6)]  [ASC |DESC]) ;\r\n                             \r\n#\u5220\u9664\u7d22\u5f15\uff1a\r\n        DROP INDEX \u7d22\u5f15\u540d ON \u8868\u540d\u5b57;\r\n        alter table country drop index \u7d22\u5f15\u540d\u5b57;\r\n\r\n#\u67e5\u770b\u7d22\u5f15\r\n\u65b9\u6cd5\u4e00\uff1a\r\nmysql> desc t1;\r\n+-----+\r\n| Key |\r\n+-----+\r\n| PRI | \u4e3b\u952e\u7d22\u5f15\r\n| MUL | \u666e\u901a\u7d22\u5f15\r\n| UNI | \u552f\u4e00\u952e\u7d22\u5f15\r\n+-----+\r\n\r\n\u65b9\u6cd5\u4e8c\uff1a\r\nmysql> show index from t1;<\/pre>\n<\/div>\n
\u521b\u5efa\u7d22\u5f15\u793a\u8303<\/p>\n
\n
\n
#\u65b9\u5f0f\u4e00\r\ncreate table t1(\r\n    id int,\r\n    name char,\r\n    age int,\r\n    sex enum('male','female'),\r\n    unique key uni_id(id),\r\n    index ix_name(name) #index\u6ca1\u6709key\r\n);\r\n\r\n\r\n#\u65b9\u5f0f\u4e8c\r\ncreate index ix_age on t1(age);\r\n\r\n#\u65b9\u5f0f\u4e09\r\nalter table t1 add index ix_sex(sex);\r\n\r\n#\u67e5\u770b\r\nmysql> show create table t1;\r\n| t1    | CREATE TABLE `t1` (\r\n  `id` int(11) DEFAULT NULL,\r\n  `name` char(1) DEFAULT NULL,\r\n  `age` int(11) DEFAULT NULL,\r\n  `sex` enum('male','female') DEFAULT NULL,\r\n  UNIQUE KEY `uni_id` (`id`),\r\n  KEY `ix_name` (`name`),\r\n  KEY `ix_age` (`age`),\r\n  KEY `ix_sex` (`sex`)\r\n) ENGINE=InnoDB DEFAULT CHARSET=latin1<\/pre>\n<\/div>\n<\/div>\n
\u521b\u5efa\u7d22\u5f15\u793a\u8303<\/strong><\/p>\n
\n
# 1\uff09\u4e3b\u952e\u7d22\u5f15(\u805a\u96c6\u7d22\u5f15)\r\n#\u521b\u5efa\u4e3b\u952e\u7d22\u5f15\r\nmysql> alter table student add primary key pri_id(id);\r\nmysql> create table student(id int not null, primary key(id)); \r\nmysql> create table student(id int not null primary key auto_increment comment '\u5b66\u53f7');\r\n\r\n#\u63d0\u793a\uff1a\r\ndatabase\u53ef\u4ee5\u5199 schema\r\nindex\u53ef\u4ee5\u5199 key\r\n\r\n\r\n# 2\uff09\u552f\u4e00\u952e\u7d22\u5f15\r\n#\u521b\u5efa\u552f\u4e00\u5efa\u7d22\u5f15\r\nmysql> alter table country add unique key uni_name(name);\r\nmysql> create table student(id int unique key comment '\u5b66\u53f7');\r\nmysql> create unique key index index_name on table_name(id);\r\n\r\n\r\n# 3\uff09\u666e\u901a\u7d22\u5f15(\u8f85\u52a9\u7d22\u5f15)\r\n#\u666e\u901a\u7d22\u5f15\u7684\u521b\u5efa\r\nmysql> alter table student add index idx_gender(gender);\r\nCREATE INDEX index_name ON table_name (column_list);\r\n\r\n\r\n# 4\uff09\u521b\u5efa\u524d\u7f00\u7d22\u5f15\r\n\u6309\u7167\u8be5\u5217\u6570\u636e\u7684\u524dn\u4e2a\u5b57\u6bcd\u521b\u5efa\u7d22\u5f15\r\nmysql> alter table student add index idx_name(name(4));\r\n\r\n\r\n# 5\uff09\u5168\u6587\u7d22\u5f15\r\n#\u9488\u5bf9content\u505a\u4e86\u5168\u6587\u7d22\u5f15\uff1a\r\nCREATE TABLE t1 (\r\nid int NOT NULL AUTO_INCREMENT,\r\ntitle char(255) NOT NULL,\r\ncontent text,\r\nPRIMARY KEY (id),\r\nFULLTEXT (content));\r\n\r\n\u67e5\u627e\u65f6\uff1a\r\nselect * from table where match(content) against('\u60f3\u67e5\u8be2\u7684\u5b57\u7b26\u4e32');<\/pre>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
\u4e00 \u529f\u80fd #1. \u7d22\u5f15\u7684\u529f\u80fd\u5c31\u662f\u52a0\u901f\u67e5\u627e #2. mysql\u4e2d\u7684primary key\uff0cunique\uff0c\u8054\u5408\u552f\u4e00 […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/egonlin.com\/index.php?rest_route=\/wp\/v2\/posts\/11451"}],"collection":[{"href":"https:\/\/egonlin.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/egonlin.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/egonlin.com\/index.php?rest_route=\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/egonlin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=11451"}],"version-history":[{"count":3,"href":"https:\/\/egonlin.com\/index.php?rest_route=\/wp\/v2\/posts\/11451\/revisions"}],"predecessor-version":[{"id":11454,"href":"https:\/\/egonlin.com\/index.php?rest_route=\/wp\/v2\/posts\/11451\/revisions\/11454"}],"wp:attachment":[{"href":"https:\/\/egonlin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=11451"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/egonlin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=11451"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/egonlin.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=11451"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}