\u6ce8\u610f\uff1a\u4e3a\u8be5\u7c7b\u578b\u6307\u5b9a\u5bbd\u5ea6\u65f6\uff0c\u4ec5\u4ec5\u53ea\u662f\u6307\u5b9a\u67e5\u8be2\u7ed3\u679c\u7684\u663e\u793a\u5bbd\u5ea6\uff0c\u4e0e\u5b58\u50a8\u8303\u56f4\u65e0\u5173\uff0c\u5b58\u50a8\u8303\u56f4\u5982\u4e0b<\/p>\n
\u5176\u5b9e\u6211\u4eec\u5b8c\u5168\u6ca1\u5fc5\u8981\u4e3a\u6574\u6570\u7c7b\u578b\u6307\u5b9a\u663e\u793a\u5bbd\u5ea6\uff0c\u4f7f\u7528\u9ed8\u8ba4\u7684\u5c31\u53ef\u4ee5\u4e86<\/p>\n
\u9ed8\u8ba4\u7684\u663e\u793a\u5bbd\u5ea6\uff0c\u90fd\u662f\u5728\u6700\u5927\u503c\u7684\u57fa\u7840\u4e0a\u52a01
\n
![\"file\"](\"data:image\/svg+xml;base64,PCEtLUFyZ29uTG9hZGluZy0tPgo8c3ZnIHdpZHRoPSIxIiBoZWlnaHQ9IjEiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgc3Ryb2tlPSIjZmZmZmZmMDAiPjxnPjwvZz4KPC9zdmc+\")
<\/div>
\nnt\u7684\u5b58\u50a8\u5bbd\u5ea6\u662f4\u4e2aBytes\uff0c\u537332\u4e2abit\uff0c\u53732**32<\/p>\n
\u65e0\u7b26\u53f7\u6700\u5927\u503c\u4e3a\uff1a4294967296-1<\/p>\n
\u6709\u7b26\u53f7\u6700\u5927\u503c\uff1a2147483648-1<\/p>\n
\u6709\u7b26\u53f7\u548c\u65e0\u7b26\u53f7\u7684\u6700\u5927\u6570\u5b57\u9700\u8981\u7684\u663e\u793a\u5bbd\u5ea6\u5747\u4e3a10\uff0c\u800c\u9488\u5bf9\u6709\u7b26\u53f7\u7684\u6700\u5c0f\u503c\u5219\u9700\u898111\u4f4d\u624d\u80fd\u663e\u793a\u5b8c\u5168\uff0c\u6240\u4ee5int\u7c7b\u578b\u9ed8\u8ba4\u7684\u663e\u793a\u5bbd\u5ea6\u4e3a11\u662f\u975e\u5e38\u5408\u7406\u7684<\/p>\n
\u6700\u540e\uff1a\u6574\u5f62\u7c7b\u578b\uff0c\u5176\u5b9e\u6ca1\u6709\u5fc5\u8981\u6307\u5b9a\u663e\u793a\u5bbd\u5ea6\uff0c\u4f7f\u7528\u9ed8\u8ba4\u7684\u5c31ok<\/p>\n
2\u3001\u6d6e\u70b9\u578b<\/h4>\n
\u5b9a\u70b9\u6570\u7c7b\u578b DEC\u7b49\u540c\u4e8eDECIMAL\u3000\u3000<\/p>\n
\u6d6e\u70b9\u7c7b\u578b\uff1aFLOAT DOUBLE<\/p>\n
\u4f5c\u7528\uff1a\u5b58\u50a8\u85aa\u8d44\u3001\u8eab\u9ad8\u3001\u4f53\u91cd\u3001\u4f53\u8d28\u53c2\u6570\u7b49<\/p>\n
======================================\n#FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]\n\n\u5b9a\u4e49\uff1a\n \u5355\u7cbe\u5ea6\u6d6e\u70b9\u6570\uff08\u975e\u51c6\u786e\u5c0f\u6570\u503c\uff09\uff0cm\u662f\u6570\u5b57\u603b\u4e2a\u6570\uff0cd\u662f\u5c0f\u6570\u70b9\u540e\u4e2a\u6570\u3002m\u6700\u5927\u503c\u4e3a255\uff0cd\u6700\u5927\u503c\u4e3a30\n\n\u6709\u7b26\u53f7\uff1a\n -3.402823466E+38 to -1.175494351E-38,\n 1.175494351E-38 to 3.402823466E+38\n\u65e0\u7b26\u53f7\uff1a\n 1.175494351E-38 to 3.402823466E+38\n\n\u7cbe\u786e\u5ea6\uff1a \n **** \u968f\u7740\u5c0f\u6570\u7684\u589e\u591a\uff0c\u7cbe\u5ea6\u53d8\u5f97\u4e0d\u51c6\u786e ****\n\n======================================\n#DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]\n\n\u5b9a\u4e49\uff1a\n \u53cc\u7cbe\u5ea6\u6d6e\u70b9\u6570\uff08\u975e\u51c6\u786e\u5c0f\u6570\u503c\uff09\uff0cm\u662f\u6570\u5b57\u603b\u4e2a\u6570\uff0cd\u662f\u5c0f\u6570\u70b9\u540e\u4e2a\u6570\u3002m\u6700\u5927\u503c\u4e3a255\uff0cd\u6700\u5927\u503c\u4e3a30\n\n\u6709\u7b26\u53f7\uff1a\n -1.7976931348623157E+308 to -2.2250738585072014E-308\n 2.2250738585072014E-308 to 1.7976931348623157E+308\n\n\u65e0\u7b26\u53f7\uff1a\n 2.2250738585072014E-308 to 1.7976931348623157E+308\n\n\u7cbe\u786e\u5ea6\uff1a\n ****\u968f\u7740\u5c0f\u6570\u7684\u589e\u591a\uff0c\u7cbe\u5ea6\u6bd4float\u8981\u9ad8\uff0c\u4f46\u4e5f\u4f1a\u53d8\u5f97\u4e0d\u51c6\u786e ****\n\n======================================\ndecimal[(m[,d])] [unsigned] [zerofill]\n\n\u5b9a\u4e49\uff1a\n \u51c6\u786e\u7684\u5c0f\u6570\u503c\uff0cm\u662f\u6570\u5b57\u603b\u4e2a\u6570\uff08\u8d1f\u53f7\u4e0d\u7b97\uff09\uff0cd\u662f\u5c0f\u6570\u70b9\u540e\u4e2a\u6570\u3002 m\u6700\u5927\u503c\u4e3a65\uff0cd\u6700\u5927\u503c\u4e3a30\u3002\n\n\u7cbe\u786e\u5ea6\uff1a\n **** \u968f\u7740\u5c0f\u6570\u7684\u589e\u591a\uff0c\u7cbe\u5ea6\u59cb\u7ec8\u51c6\u786e ****\n \u5bf9\u4e8e\u7cbe\u786e\u6570\u503c\u8ba1\u7b97\u65f6\u9700\u8981\u7528\u6b64\u7c7b\u578b\n decaimal\u80fd\u591f\u5b58\u50a8\u7cbe\u786e\u503c\u7684\u539f\u56e0\u5728\u4e8e\u5176\u5185\u90e8\u6309\u7167\u5b57\u7b26\u4e32\u5b58\u50a8\u3002<\/code><\/pre>\n\u9a8c\u8bc1<\/p>\n
mysql> create table t1(x float(256,31));\nERROR 1425 (42000): Too big scale 31 specified for column 'x'. Maximum is 30.\nmysql> create table t1(x float(256,30));\nERROR 1439 (42000): Display width out of range for column 'x' (max = 255)\nmysql> create table t1(x float(255,30)); #\u5efa\u8868\u6210\u529f\nQuery OK, 0 rows affected (0.02 sec)\n\nmysql> create table t2(x double(255,30)); #\u5efa\u8868\u6210\u529f\nQuery OK, 0 rows affected (0.02 sec)\n\nmysql> create table t3(x decimal(66,31));\nERROR 1425 (42000): Too big scale 31 specified for column 'x'. Maximum is 30.\nmysql> create table t3(x decimal(66,30));\nERROR 1426 (42000): Too-big precision 66 specified for 'x'. Maximum is 65.\nmysql> create table t3(x decimal(65,30)); #\u5efa\u8868\u6210\u529f\nQuery OK, 0 rows affected (0.02 sec)\n\nmysql> show tables;\n+---------------+\n| Tables_in_db1 |\n+---------------+\n| t1 |\n| t2 |\n| t3 |\n+---------------+\n3 rows in set (0.00 sec)\n\nmysql> insert into t1 values(1.1111111111111111111111111111111); #\u5c0f\u6570\u70b9\u540e31\u4e2a1\nQuery OK, 1 row affected (0.01 sec)\n\nmysql> insert into t2 values(1.1111111111111111111111111111111);\nQuery OK, 1 row affected (0.00 sec)\n\nmysql> insert into t3 values(1.1111111111111111111111111111111);\nQuery OK, 1 row affected, 1 warning (0.01 sec)\n\nmysql> select * from t1; #\u968f\u7740\u5c0f\u6570\u7684\u589e\u591a\uff0c\u7cbe\u5ea6\u5f00\u59cb\u4e0d\u51c6\u786e\n+----------------------------------+\n| x |\n+----------------------------------+\n| 1.111111164093017600000000000000 |\n+----------------------------------+\n1 row in set (0.00 sec)\n\nmysql> select * from t2; #\u7cbe\u5ea6\u6bd4float\u8981\u51c6\u786e\u70b9\uff0c\u4f46\u968f\u7740\u5c0f\u6570\u7684\u589e\u591a\uff0c\u540c\u6837\u53d8\u5f97\u4e0d\u51c6\u786e\n+----------------------------------+\n| x |\n+----------------------------------+\n| 1.111111111111111200000000000000 |\n+----------------------------------+\n1 row in set (0.00 sec)\n\nmysql> select * from t3; #\u7cbe\u5ea6\u59cb\u7ec8\u51c6\u786e,d\u4e3a30\uff0c\u4e8e\u662f\u53ea\u7559\u4e8630\u4f4d\u5c0f\u6570\n+----------------------------------+\n| x |\n+----------------------------------+\n| 1.111111111111111111111111111111 |\n+----------------------------------+\n1 row in set (0.00 sec)<\/code><\/pre>\n3\u3001\u4f4d\u7c7b\u578b\uff08\u4e86\u89e3\uff09<\/h4>\n
BIT(M)\u53ef\u4ee5\u7528\u6765\u5b58\u653e\u591a\u4f4d\u4e8c\u8fdb\u5236\u6570\uff0cM\u8303\u56f4\u4ece1~64\uff0c\u5982\u679c\u4e0d\u5199\u9ed8\u8ba4\u4e3a1\u4f4d\u3002
\n\u6ce8\u610f\uff1a\u5bf9\u4e8e\u4f4d\u5b57\u6bb5\u9700\u8981\u4f7f\u7528\u51fd\u6570\u8bfb\u53d6
\nbin()\u663e\u793a\u4e3a\u4e8c\u8fdb\u5236
\nhex()\u663e\u793a\u4e3a\u5341\u516d\u8fdb\u5236
\n\u9a8c\u8bc1<\/p>\n
MariaDB [db1]> create table t9(id bit);\nMariaDB [db1]> desc t9; #bit\u9ed8\u8ba4\u5bbd\u5ea6\u4e3a1\n+-------+--------+------+-----+---------+-------+\n| Field | Type | Null | Key | Default | Extra |\n+-------+--------+------+-----+---------+-------+\n| id | bit(1) | YES | | NULL | |\n+-------+--------+------+-----+---------+-------+\n\nMariaDB [db1]> insert into t9 values(8);\nMariaDB [db1]> select * from t9; #\u76f4\u63a5\u67e5\u770b\u662f\u65e0\u6cd5\u663e\u793a\u4e8c\u8fdb\u5236\u4f4d\u7684\n+------+\n| id |\n+------+\n| |\n+------+\nMariaDB [db1]> select bin(id),hex(id) from t9; #\u9700\u8981\u8f6c\u6362\u624d\u80fd\u770b\u5230\n+---------+---------+\n| bin(id) | hex(id) |\n+---------+---------+\n| 1 | 1 |\n+---------+---------+\n\nMariaDB [db1]> alter table t9 modify id bit(5);\nMariaDB [db1]> insert into t9 values(8);\nMariaDB [db1]> select bin(id),hex(id) from t9;\n+---------+---------+\n| bin(id) | hex(id) |\n+---------+---------+\n| 1 | 1 |\n| 1000 | 8 |\n+---------+---------+<\/code><\/pre>\n5.3 \u65e5\u671f\u7c7b\u578b<\/h3>\n
DATE TIME DATETIME TIMESTAMP YEAR <\/p>\n
\u4f5c\u7528\uff1a\u5b58\u50a8\u7528\u6237\u6ce8\u518c\u65f6\u95f4\uff0c\u6587\u7ae0\u53d1\u5e03\u65f6\u95f4\uff0c\u5458\u5de5\u5165\u804c\u65f6\u95f4\uff0c\u51fa\u751f\u65f6\u95f4\uff0c\u8fc7\u671f\u65f6\u95f4\u7b49<\/p>\n
YEAR\n YYYY\uff081901\/2155\uff09\n\n DATE\n YYYY-MM-DD\uff081000-01-01\/9999-12-31\uff09\n\n TIME\n HH:MM:SS\uff08'-838:59:59'\/'838:59:59'\uff09\n\n DATETIME\n\n YYYY-MM-DD HH:MM:SS\uff081000-01-01 00:00:00\/9999-12-31 23:59:59 Y\uff09\n\n TIMESTAMP\n\n YYYYMMDD HHMMSS\uff081970-01-01 00:00:00\/2037 \u5e74\u67d0\u65f6\uff09<\/code><\/pre>\n\u9a8c\u8bc1<\/p>\n
============year===========\nMariaDB [db1]> create table t10(born_year year); #\u65e0\u8bbayear\u6307\u5b9a\u4f55\u79cd\u5bbd\u5ea6\uff0c\u6700\u540e\u90fd\u9ed8\u8ba4\u662fyear(4)\nMariaDB [db1]> insert into t10 values \n -> (1900),\n -> (1901),\n -> (2155),\n -> (2156);\nMariaDB [db1]> select * from t10;\n+-----------+\n| born_year |\n+-----------+\n| 0000 |\n| 1901 |\n| 2155 |\n| 0000 |\n+-----------+\n\n============date,time,datetime===========\nMariaDB [db1]> create table t11(d date,t time,dt datetime);\nMariaDB [db1]> desc t11;\n+-------+----------+------+-----+---------+-------+\n| Field | Type | Null | Key | Default | Extra |\n+-------+----------+------+-----+---------+-------+\n| d | date | YES | | NULL | |\n| t | time | YES | | NULL | |\n| dt | datetime | YES | | NULL | |\n+-------+----------+------+-----+---------+-------+\n\nMariaDB [db1]> insert into t11 values(now(),now(),now());\nMariaDB [db1]> select * from t11;\n+------------+----------+---------------------+\n| d | t | dt |\n+------------+----------+---------------------+\n| 2017-07-25 | 16:26:54 | 2017-07-25 16:26:54 |\n+------------+----------+---------------------+\n\n============timestamp===========\nMariaDB [db1]> create table t12(time timestamp);\nMariaDB [db1]> insert into t12 values();\nMariaDB [db1]> insert into t12 values(null);\nMariaDB [db1]> select * from t12;\n+---------------------+\n| time |\n+---------------------+\n| 2017-07-25 16:29:17 |\n| 2017-07-25 16:30:01 |\n+---------------------+\n\n============\u6ce8\u610f\u5566\uff0c\u6ce8\u610f\u5566\uff0c\u6ce8\u610f\u5566===========\n1. \u5355\u72ec\u63d2\u5165\u65f6\u95f4\u65f6\uff0c\u9700\u8981\u4ee5\u5b57\u7b26\u4e32\u7684\u5f62\u5f0f\uff0c\u6309\u7167\u5bf9\u5e94\u7684\u683c\u5f0f\u63d2\u5165\n2. \u63d2\u5165\u5e74\u4efd\u65f6\uff0c\u5c3d\u91cf\u4f7f\u75284\u4f4d\u503c\n3. \u63d2\u5165\u4e24\u4f4d\u5e74\u4efd\u65f6\uff0c<=69\uff0c\u4ee520\u5f00\u5934\uff0c\u6bd4\u598250, \u7ed3\u679c2050 \n >=70\uff0c\u4ee519\u5f00\u5934\uff0c\u6bd4\u598271\uff0c\u7ed3\u679c1971\nMariaDB [db1]> create table t12(y year);\nMariaDB [db1]> insert into t12 values \n -> (50),\n -> (71);\nMariaDB [db1]> select * from t12;\n+------+\n| y |\n+------+\n| 2050 |\n| 1971 |\n+------+\n\n============\u7efc\u5408\u7ec3\u4e60===========\nMariaDB [db1]> create table student(\n -> id int,\n -> name varchar(20),\n -> born_year year,\n -> birth date,\n -> class_time time,\n -> reg_time datetime);\n\nMariaDB [db1]> insert into student values\n -> (1,'alex',"1995","1995-11-11","11:11:11","2017-11-11 11:11:11"),\n -> (2,'egon',"1997","1997-12-12","12:12:12","2017-12-12 12:12:12"),\n -> (3,'wsb',"1998","1998-01-01","13:13:13","2017-01-01 13:13:13");\n\nMariaDB [db1]> select * from student;\n+------+------+-----------+------------+------------+---------------------+\n| id | name | born_year | birth | class_time | reg_time |\n+------+------+-----------+------------+------------+---------------------+\n| 1 | alex | 1995 | 1995-11-11 | 11:11:11 | 2017-11-11 11:11:11 |\n| 2 | egon | 1997 | 1997-12-12 | 12:12:12 | 2017-12-12 12:12:12 |\n| 3 | wsb | 1998 | 1998-01-01 | 13:13:13 | 2017-01-01 13:13:13 |\n+------+------+-----------+------------+------------+---------------------+\n<\/code><\/pre>\ndatetime\u4e0etimestamp\u7684\u533a\u522b<\/p>\n
\u5728\u5b9e\u9645\u5e94\u7528\u7684\u5f88\u591a\u573a\u666f\u4e2d\uff0cMySQL\u7684\u8fd9\u4e24\u79cd\u65e5\u671f\u7c7b\u578b\u90fd\u80fd\u591f\u6ee1\u8db3\u6211\u4eec\u7684\u9700\u8981\uff0c\u5b58\u50a8\u7cbe\u5ea6\u90fd\u4e3a\u79d2\uff0c\u4f46\u5728\u67d0\u4e9b\u60c5\u51b5\u4e0b\uff0c\u4f1a\u5c55\u73b0\u51fa\u4ed6\u4eec\u5404\u81ea\u7684\u4f18\u52a3\u3002\u4e0b\u9762\u5c31\u6765\u603b\u7ed3\u4e00\u4e0b\u4e24\u79cd\u65e5\u671f\u7c7b\u578b\u7684\u533a\u522b\u3002\n\n1.DATETIME\u7684\u65e5\u671f\u8303\u56f4\u662f1001\u2014\u20149999\u5e74\uff0cTIMESTAMP\u7684\u65f6\u95f4\u8303\u56f4\u662f1970\u2014\u20142038\u5e74\u3002\n\n2.DATETIME\u5b58\u50a8\u65f6\u95f4\u4e0e\u65f6\u533a\u65e0\u5173\uff0cTIMESTAMP\u5b58\u50a8\u65f6\u95f4\u4e0e\u65f6\u533a\u6709\u5173\uff0c\u663e\u793a\u7684\u503c\u4e5f\u4f9d\u8d56\u4e8e\u65f6\u533a\u3002\u5728mysql\u670d\u52a1\u5668\uff0c\u64cd\u4f5c\u7cfb\u7edf\u4ee5\u53ca\u5ba2\u6237\u7aef\u8fde\u63a5\u90fd\u6709\u65f6\u533a\u7684\u8bbe\u7f6e\u3002\n\n3.DATETIME\u4f7f\u75288\u5b57\u8282\u7684\u5b58\u50a8\u7a7a\u95f4\uff0cTIMESTAMP\u7684\u5b58\u50a8\u7a7a\u95f4\u4e3a4\u5b57\u8282\u3002\u56e0\u6b64\uff0cTIMESTAMP\u6bd4DATETIME\u7684\u7a7a\u95f4\u5229\u7528\u7387\u66f4\u9ad8\u3002\n\n4.DATETIME\u7684\u9ed8\u8ba4\u503c\u4e3anull\uff1bTIMESTAMP\u7684\u5b57\u6bb5\u9ed8\u8ba4\u4e0d\u4e3a\u7a7a\uff08not null\uff09,\u9ed8\u8ba4\u503c\u4e3a\u5f53\u524d\u65f6\u95f4\uff08CURRENT_TIMESTAMP\uff09\uff0c\u5982\u679c\u4e0d\u505a\u7279\u6b8a\u5904\u7406\uff0c\u5e76\u4e14update\u8bed\u53e5\u4e2d\u6ca1\u6709\u6307\u5b9a\u8be5\u5217\u7684\u66f4\u65b0\u503c\uff0c\u5219\u9ed8\u8ba4\u66f4\u65b0\u4e3a\u5f53\u524d\u65f6\u95f4\u3002<\/code><\/pre>\n\u9a8c\u8bc1<\/p>\n
mysql> create table t1(x datetime not null default now()); # \u9700\u8981\u6307\u5b9a\u4f20\u5165\u7a7a\u503c\u65f6\u9ed8\u8ba4\u53d6\u5f53\u524d\u65f6\u95f4\nQuery OK, 0 rows affected (0.01 sec)\n\nmysql> create table t2(x timestamp); # \u65e0\u9700\u4efb\u4f55\u8bbe\u7f6e\uff0c\u5728\u4f20\u7a7a\u503c\u7684\u60c5\u51b5\u4e0b\u81ea\u52a8\u4f20\u5165\u5f53\u524d\u65f6\u95f4\nQuery OK, 0 rows affected (0.02 sec)\n\nmysql> insert into t1 values();\nQuery OK, 1 row affected (0.00 sec)\n\nmysql> insert into t2 values();\nQuery OK, 1 row affected (0.00 sec)\n\nmysql> select * from t1;\n+---------------------+\n| x |\n+---------------------+\n| 2018-07-07 01:26:14 |\n+---------------------+\n1 row in set (0.00 sec)\n\nmysql> select * from t2;\n+---------------------+\n| x |\n+---------------------+\n| 2018-07-07 01:26:17 |\n+---------------------+\n1 row in set (0.00 sec)<\/code><\/pre>\n\uff01\uff01\uff01\u6ce8\u610f\uff1a\u9488\u5bf9datetime\u6216\u8005timestamp\u5982\u679c\u662f\u7528\u4f5c\u6ce8\u518c\u65f6\u95f4\uff0c\u90a3\u4e48\u6307\u5b9anot null default now()\u81ea\u52a8\u586b\u5145\u65f6\u95f4\u5373\u53ef\uff0c\u5982\u679c\u662f\u7528\u4f5c\u66f4\u65b0\u65f6\u95f4\u90a3\u4e48\u9700\u8981\u989d\u5916\u6307\u5b9aon update now()\uff0c\u8be5\u914d\u7f6etimestamp\u81ea\u5e26<\/p>\n
mysql> create table t9(name varchar(5),x datetime not null default now());\nQuery OK, 0 rows affected (0.01 sec)\n\nmysql> create table t10(name varchar(5),x datetime not null default now() on update now());\nQuery OK, 0 rows affected (0.01 sec)\n\nmysql> create table t11(name varchar(5),x timestamp);\nQuery OK, 0 rows affected (0.02 sec)\n\nmysql> \nmysql> desc t9\n -> ;\n+-------+------------+------+-----+-------------------+-------+\n| Field | Type | Null | Key | Default | Extra |\n+-------+------------+------+-----+-------------------+-------+\n| name | varchar(5) | YES | | NULL | |\n| x | datetime | NO | | CURRENT_TIMESTAMP | |\n+-------+------------+------+-----+-------------------+-------+\n2 rows in set (0.01 sec)\n\nmysql> desc t10\n -> ;\n+-------+------------+------+-----+-------------------+-----------------------------+\n| Field | Type | Null | Key | Default | Extra |\n+-------+------------+------+-----+-------------------+-----------------------------+\n| name | varchar(5) | YES | | NULL | |\n| x | datetime | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |\n+-------+------------+------+-----+-------------------+-----------------------------+\n2 rows in set (0.01 sec)\n\nmysql> desc t11;\n+-------+------------+------+-----+-------------------+-----------------------------+\n| Field | Type | Null | Key | Default | Extra |\n+-------+------------+------+-----+-------------------+-----------------------------+\n| name | varchar(5) | YES | | NULL | |\n| x | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |\n+-------+------------+------+-----+-------------------+-----------------------------+\n2 rows in set (0.01 sec)\n\nmysql> mysql> into t9(name) values("egon");\nQuery OK, 1 row affected (0.01 sec)\n\nmysql> insert into t10(name) values("egon");\nQuery OK, 1 row affected (0.00 sec)\n\nmysql> insert into t11(name) values("egon");\nQuery OK, 1 row affected (0.00 sec)\n\nmysql> \nmysql> select * from t9;\n+------+---------------------+\n| name | x |\n+------+---------------------+\n| egon | 2020-09-01 13:54:25 |\n+------+---------------------+\n1 row in set (0.00 sec)\n\nmysql> select * from t10;\n+------+---------------------+\n| name | x |\n+------+---------------------+\n| egon | 2020-09-01 13:54:35 |\n+------+---------------------+\n1 row in set (0.00 sec)\n\nmysql> select * from t11;\n+------+---------------------+\n| name | x |\n+------+---------------------+\n| egon | 2020-09-01 13:54:39 |\n+------+---------------------+\n1 row in set (0.00 sec)\n\nmysql> update t9 set name="EGON" where name="egon";\nQuery OK, 1 row affected (0.01 sec)\nRows matched: 1 Changed: 1 Warnings: 0\n\nmysql> update t10 set name="EGON" where name="egon";\nQuery OK, 1 row affected (0.00 sec)\nRows matched: 1 Changed: 1 Warnings: 0\n\nmysql> update t11 set name="EGON" where name="egon";\nQuery OK, 1 row affected (0.01 sec)\nRows matched: 1 Changed: 1 Warnings: 0\n\nmysql> select * from t9;\n+------+---------------------+\n| name | x |\n+------+---------------------+\n| EGON | 2020-09-01 13:54:25 |\n+------+---------------------+\n1 row in set (0.00 sec)\n\nmysql> select * from t10;\n+------+---------------------+\n| name | x |\n+------+---------------------+\n| EGON | 2020-09-01 13:56:22 |\n+------+---------------------+\n1 row in set (0.00 sec)\n\nmysql> select * from t11;\n+------+---------------------+\n| name | x |\n+------+---------------------+\n| EGON | 2020-09-01 13:56:26 |\n+------+---------------------+\n1 row in set (0.00 sec)\n\nmysql> <\/code><\/pre>\n5.4 \u5b57\u7b26\u4e32\u7c7b\u578b<\/h3>\n#\u5b98\u7f51\uff1ahttps:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/char.html\n#\u6ce8\u610f\uff1achar\u548cvarchar\u62ec\u53f7\u5185\u7684\u53c2\u6570\u6307\u7684\u90fd\u662f\u5b57\u7b26\u7684\u957f\u5ea6\n\n#char\u7c7b\u578b\uff1a\u5b9a\u957f\uff0c\u7b80\u5355\u7c97\u66b4\uff0c\u6d6a\u8d39\u7a7a\u95f4\uff0c\u5b58\u53d6\u901f\u5ea6\u5feb\n \u5b57\u7b26\u957f\u5ea6\u8303\u56f4\uff1a0-255\uff08\u4e00\u4e2a\u4e2d\u6587\u662f\u4e00\u4e2a\u5b57\u7b26\uff0c\u662futf8\u7f16\u7801\u76843\u4e2a\u5b57\u8282\uff09\n \u5b58\u50a8\uff1a\n \u5b58\u50a8char\u7c7b\u578b\u7684\u503c\u65f6\uff0c\u4f1a\u5f80\u53f3\u586b\u5145\u7a7a\u683c\u6765\u6ee1\u8db3\u957f\u5ea6\n \u4f8b\u5982\uff1a\u6307\u5b9a\u957f\u5ea6\u4e3a10\uff0c\u5b58>10\u4e2a\u5b57\u7b26\u5219\u62a5\u9519\uff0c\u5b58<10\u4e2a\u5b57\u7b26\u5219\u7528\u7a7a\u683c\u586b\u5145\u76f4\u5230\u51d1\u591f10\u4e2a\u5b57\u7b26\u5b58\u50a8\n\n \u68c0\u7d22\uff1a\n \u5728\u68c0\u7d22\u6216\u8005\u8bf4\u67e5\u8be2\u65f6\uff0c\u67e5\u51fa\u7684\u7ed3\u679c\u4f1a\u81ea\u52a8\u5220\u9664\u5c3e\u90e8\u7684\u7a7a\u683c\uff0c\u9664\u975e\u6211\u4eec\u6253\u5f00pad_char_to_full_length SQL\u6a21\u5f0f\uff08SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';\uff09\n\n#varchar\u7c7b\u578b\uff1a\u53d8\u957f\uff0c\u7cbe\u51c6\uff0c\u8282\u7701\u7a7a\u95f4\uff0c\u5b58\u53d6\u901f\u5ea6\u6162\n \u5b57\u7b26\u957f\u5ea6\u8303\u56f4\uff1a0-65535\uff08\u5982\u679c\u5927\u4e8e21845\u4f1a\u63d0\u793a\u7528\u5176\u4ed6\u7c7b\u578b \u3002mysql\u884c\u6700\u5927\u9650\u5236\u4e3a65535\u5b57\u8282\uff0c\u5b57\u7b26\u7f16\u7801\u4e3autf-8\uff1ahttps:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/column-count-limit.html\uff09\n \u5b58\u50a8\uff1a\n varchar\u7c7b\u578b\u5b58\u50a8\u6570\u636e\u7684\u771f\u5b9e\u5185\u5bb9\uff0c\u4e0d\u4f1a\u7528\u7a7a\u683c\u586b\u5145\uff0c\u5982\u679c'ab ',\u5c3e\u90e8\u7684\u7a7a\u683c\u4e5f\u4f1a\u88ab\u5b58\u8d77\u6765\n \u5f3a\u8c03\uff1avarchar\u7c7b\u578b\u4f1a\u5728\u771f\u5b9e\u6570\u636e\u524d\u52a01-2Bytes\u7684\u524d\u7f00\uff0c\u8be5\u524d\u7f00\u7528\u6765\u8868\u793a\u771f\u5b9e\u6570\u636e\u7684bytes\u5b57\u8282\u6570\uff081-2Bytes\u6700\u5927\u8868\u793a65535\u4e2a\u6570\u5b57\uff0c\u6b63\u597d\u7b26\u5408mysql\u5bf9row\u7684\u6700\u5927\u5b57\u8282\u9650\u5236\uff0c\u5373\u5df2\u7ecf\u8db3\u591f\u4f7f\u7528\uff09\n \u5982\u679c\u771f\u5b9e\u7684\u6570\u636e<255bytes\u5219\u9700\u89811Bytes\u7684\u524d\u7f00\uff081Bytes=8bit 2**8\u6700\u5927\u8868\u793a\u7684\u6570\u5b57\u4e3a255\uff09\n \u5982\u679c\u771f\u5b9e\u7684\u6570\u636e>255bytes\u5219\u9700\u89812Bytes\u7684\u524d\u7f00\uff082Bytes=16bit 2**16\u6700\u5927\u8868\u793a\u7684\u6570\u5b57\u4e3a65535\uff09\n\n \u68c0\u7d22\uff1a\n \u5c3e\u90e8\u6709\u7a7a\u683c\u4f1a\u4fdd\u5b58\u4e0b\u6765\uff0c\u5728\u68c0\u7d22\u6216\u8005\u8bf4\u67e5\u8be2\u65f6\uff0c\u4e5f\u4f1a\u6b63\u5e38\u663e\u793a\u5305\u542b\u7a7a\u683c\u5728\u5185\u7684\u5185\u5bb9<\/code><\/pre>\n\u5b98\u7f51\u8be6\u89e3<\/p>\n
#\u5b98\u7f51\uff1ahttps:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/char.html\nCHAR \u548c VARCHAR \u662f\u6700\u5e38\u4f7f\u7528\u7684\u4e24\u79cd\u5b57\u7b26\u4e32\u7c7b\u578b\u3002\n\u4e00\u822c\u6765\u8bf4\nCHAR(N)\u7528\u6765\u4fdd\u5b58\u56fa\u5b9a\u957f\u5ea6\u7684\u5b57\u7b26\u4e32\uff0c\u5bf9\u4e8e CHAR \u7c7b\u578b,N \u7684\u8303\u56f4 \u4e3a 0 ~ 255\nVARCHAR(N)\u7528\u6765\u4fdd\u5b58\u53d8\u957f\u5b57\u7b26\u7c7b\u578b\uff0c\u5bf9\u4e8e VARCHAR \u7c7b\u578b,N \u7684\u8303\u56f4\u4e3a 0 ~ 65 535\nCHAR(N)\u548c VARCHAR(N) \u4e2d\u7684 N \u90fd\u4ee3\u8868\u5b57\u7b26\u957f\u5ea6,\u800c\u975e\u5b57\u8282\u957f\u5ea6\u3002\nps\uff1a\u5bf9\u4e8e MySQL 4.1 \u4e4b\u524d\u7684\u7248\u672c,\u5982 MySQL 3.23 \u548c MySQL 4.0,CHAR(N)\u548c VARCHAR (N)\u4e2d\u7684 N \u4ee3\u8868\u5b57\u8282\u957f\u5ea6\u3002\n\n#CHAR\u7c7b\u578b\n\u5bf9\u4e8e CHAR \u7c7b\u578b\u7684\u5b57\u7b26\u4e32,MySQL \u6570\u636e\u5e93\u4f1a\u81ea\u52a8\u5bf9\u5b58\u50a8\u5217\u7684\u53f3\u8fb9\u8fdb\u884c\u586b\u5145(Right Padded)\u64cd\u4f5c,\u76f4\u5230\u5b57\u7b26\u4e32\u8fbe\u5230\u6307\u5b9a\u7684\u957f\u5ea6 N\u3002\u800c\u5728\u8bfb\u53d6\u8be5\u5217\u65f6,MySQL \u6570\u636e\u5e93\u4f1a\u81ea\u52a8\u5c06 \u586b\u5145\u7684\u5b57\u7b26\u5220\u9664\u3002\u6709\u4e00\u79cd\u60c5\u51b5\u4f8b\u5916,\u90a3\u5c31\u662f\u663e\u5f0f\u5730\u5c06 SQL_MODE \u8bbe\u7f6e\u4e3a PAD_CHAR_TO_ FULL_LENGTH,\u4f8b\u5982:\nmysql> CREATE TABLE t ( a CHAR(10));\n Query OK, 0 rows affected (0.03 sec)\nmysql> INSERT INTO t SELECT 'abc';\n Query OK, 1 row affected (0.03 sec)\n Records: 1 Duplicates: 0 Warnings: 0\nmysql> SELECT a,HEX(a),LENGTH(a) FROM t\\G;\n *************************** 1. row ***************************\n a: abc\n HEX(a): 616263\n LENGTH (a): 3\n 1 row in set (0.00 sec)\n mysql> SET SQL_MODE='PAD_CHAR_TO_FULL_LENGTH';\n Query OK, 0 rows affected (0.00 sec)\nmysql> SELECT a,HEX(a),LENGTH(a) FROM t\\G;\n *************************** 1. row ***************************\n a: abc\n HEX(a): 61626320202020202020\n LENGTH (a): 10\n 1 row in set (0.00 sec)\n\n\u5728\u4e0a\u8ff0\u8fd9\u4e2a\u4f8b\u5b50\u4e2d,\u5148\u521b\u5efa\u4e86\u4e00\u5f20\u8868 t,a \u5217\u7684\u7c7b\u578b\u4e3a CHAR(10)\u3002\u7136\u540e\u901a\u8fc7 INSERT\u8bed\u53e5\u63d2\u5165\u503c\u201cabc\u201d,\u56e0\u4e3a a \u5217\u7684\u7c7b\u578b\u4e3a CHAR \u578b,\u6240\u4ee5\u4f1a\u81ea\u52a8\u5728\u540e\u9762\u586b\u5145\u7a7a\u5b57\u7b26\u4e32,\u4f7f\u5176\u957f \u5ea6\u4e3a 10\u3002\u63a5\u4e0b\u6765\u5728\u901a\u8fc7 SELECT \u8bed\u53e5\u53d6\u51fa\u6570\u636e\u65f6\u4f1a\u5c06 a \u5217\u53f3\u586b\u5145\u7684\u7a7a\u5b57\u7b26\u79fb\u9664,\u4ece\u800c\u5f97\u5230 \u503c\u201cabc\u201d\u3002\u901a\u8fc7 LENGTH \u51fd\u6570\u770b\u5230 a \u5217\u7684\u5b57\u7b26\u957f\u5ea6\u4e3a 3 \u800c\u975e 10\u3002\n\u63a5\u7740\u6211\u4eec\u5c06 SQL_MODE \u663e\u5f0f\u5730\u8bbe\u7f6e\u4e3a PAD_CHAR_TO_FULL_LENGTH\u3002\u8fd9\u65f6\u518d\u901a\u8fc7 SELECT \u8bed\u53e5\u8fdb\u884c\u67e5\u8be2\u65f6,\u5f97\u5230\u7684\u7ed3\u679c\u662f\u201cabc \u201d,abc \u53f3\u8fb9\u6709 7 \u4e2a\u586b\u5145\u5b57\u7b26 0x20,\u5e76\u901a \u8fc7 HEX \u51fd\u6570\u5f97\u5230\u4e86\u9a8c\u8bc1\u3002\u8fd9\u6b21 LENGTH \u51fd\u6570\u8fd4\u56de\u7684\u957f\u5ea6\u4e3a 10\u3002\u9700\u8981\u6ce8\u610f\u7684\u662f,LENGTH \u51fd\u6570\u8fd4\u56de\u7684\u662f\u5b57\u8282\u957f\u5ea6,\u800c\u4e0d\u662f\u5b57\u7b26\u957f\u5ea6\u3002\u5bf9\u4e8e\u591a\u5b57\u8282\u5b57\u7b26\u96c6,CHAR(N)\u957f\u5ea6\u7684\u5217\u6700\u591a \u53ef\u5360\u7528\u7684\u5b57\u8282\u6570\u4e3a\u8be5\u5b57\u7b26\u96c6\u5355\u5b57\u7b26\u6700\u5927\u5360\u7528\u5b57\u8282\u6570 *N\u3002\u4f8b\u5982,\u5bf9\u4e8e utf8 \u4e0b,CHAR(10)\u6700 \u591a\u53ef\u80fd\u5360\u7528 30 \u4e2a\u5b57\u8282\u3002\u901a\u8fc7\u5bf9\u591a\u5b57\u8282\u5b57\u7b26\u4e32\u4f7f\u7528 CHAR_LENGTH \u51fd\u6570\u548c LENGTH \u51fd\u6570, \u53ef\u4ee5\u53d1\u73b0\u4e24\u8005\u7684\u4e0d\u540c,\u793a\u4f8b\u5982\u4e0b:\nmysql> SET NAMES gbk;\n Query OK, 0 rows affected (0.03 sec)\nmysql> SELECT @a:='MySQL \u6280\u672f\u5185\u5e55 '; Query OK, 0 rows affected (0.03 sec)\nmysql> SELECT @a,HEX(@a),LENGTH(@a),CHAR_LENGTH(@a)\\G; ***************************** 1. row **************************** a: MySQL \u6280\u672f\u5185\u5e55\nHEX(a): 4D7953514CBCBCCAF5C4DAC4BB\nLENGTH (a): 13\nCHAR_LENGTH(a): 9\n1 row in set (0.00 sec)\n\n\u53d8 \u91cf @ a \u662f g b k \u5b57 \u7b26 \u96c6 \u7684 \u5b57 \u7b26 \u4e32 \u7c7b \u578b , \u503c \u4e3a \u201c M y S Q L \u6280 \u672f \u5185 \u5e55 \u201d, \u5341 \u516d \u8fdb \u5236 \u4e3a 0x4D7953514CBCBCCAF5C4DAC4BB,LENGTH \u51fd\u6570\u8fd4\u56de 13,\u5373\u8be5\u5b57\u7b26\u4e32\u5360\u7528 13 \u5b57\u8282, \u56e0\u4e3a gbk \u5b57\u7b26\u96c6\u4e2d\u7684\u4e2d\u6587\u5b57\u7b26\u5360\u7528\u4e24\u4e2a\u5b57\u8282,\u56e0\u6b64\u4e00\u5171\u5360\u7528 13 \u5b57\u8282\u3002CHAR_LENGTH \u51fd\u6570 \u8fd4\u56de 9,\u5f88\u663e\u7136\u8be5\u5b57\u7b26\u957f\u5ea6\u4e3a 9\u3002\n\n#VARCHAR\u7c7b\u578b\nVARCHAR \u7c7b\u578b\u5b58\u50a8\u53d8\u957f\u5b57\u6bb5\u7684\u5b57\u7b26\u7c7b\u578b,\u4e0e CHAR \u7c7b\u578b\u4e0d\u540c\u7684\u662f,\u5176\u5b58\u50a8\u65f6\u9700\u8981\u5728 \u524d\u7f00\u957f\u5ea6\u5217\u8868\u52a0\u4e0a\u5b9e\u9645\u5b58\u50a8\u7684\u5b57\u7b26,\u8be5\u5b57\u7b26\u5360\u7528 1 ~ 2 \u5b57\u8282\u7684\u7a7a\u95f4\u3002\u5f53\u5b58\u50a8\u7684\u5b57\u7b26\u4e32\u957f\u5ea6\u5c0f \u4e8e 255 \u5b57\u8282\u65f6,\u5176\u9700\u8981 1 \u5b57\u8282\u7684\u7a7a\u95f4,\u5f53\u5927\u4e8e 255 \u5b57\u8282\u65f6,\u9700\u8981 2 \u5b57\u8282\u7684\u7a7a\u95f4\u3002\u6240\u4ee5,\u5bf9 \u4e8e\u5355\u5b57\u8282\u7684 latin1 \u6765\u8bf4,CHAR(10)\u548c VARCHAR(10)\u6700\u5927\u5360\u7528\u7684\u5b58\u50a8\u7a7a\u95f4\u662f\u4e0d\u540c\u7684, CHAR(10)\u5360\u7528 10 \u4e2a\u5b57\u8282\u8fd9\u662f\u6beb\u65e0\u7591\u95ee\u7684,\u800c VARCHAR(10)\u7684\u6700\u5927\u5360\u7528\u7a7a\u95f4\u6570\u662f 11 \u5b57\u8282,\u56e0\u4e3a\u5176\u9700\u8981 1 \u5b57\u8282\u6765\u5b58\u653e\u5b57\u7b26\u957f\u5ea6\u3002\n-------------------------------------------------\n\u6ce8\u610f \u5bf9\u4e8e\u6709\u4e9b\u591a\u5b57\u8282\u7684\u5b57\u7b26\u96c6\u7c7b\u578b,\u5176 CHAR \u548c VARCHAR \u5728\u5b58\u50a8\u65b9\u6cd5\u4e0a\u662f\u4e00\u6837\u7684,\u540c\u6837 \u9700\u8981\u4e3a\u957f\u5ea6\u5217\u8868\u52a0\u4e0a\u5b57\u7b26\u4e32\u7684\u503c\u3002\u5bf9\u4e8e GBK \u548c UTF-8 \u8fd9\u4e9b\u5b57\u7b26\u7c7b\u578b,\u5176\u6709\u4e9b\u5b57\u7b26\u662f\u4ee5 1 \u5b57\u8282 \u5b58\u653e\u7684,\u6709\u4e9b\u5b57\u7b26\u662f\u6309 2 \u6216 3 \u5b57\u8282\u5b58\u653e\u7684,\u56e0\u6b64\u540c\u6837\u9700\u8981 1 ~ 2 \u5b57\u8282\u7684\u7a7a\u95f4\u6765\u5b58\u50a8\u5b57\u7b26\u7684\u957f \u5ea6\u3002\n-------------------------------------------------\n\u867d\u7136 CHAR \u548c VARCHAR \u7684\u5b58\u50a8\u65b9\u5f0f\u4e0d\u592a\u76f8\u540c,\u4f46\u662f\u5bf9\u4e8e\u4e24\u4e2a\u5b57\u7b26\u4e32\u7684\u6bd4\u8f83,\u90fd\u53ea\u6bd4 \u8f83\u5176\u503c,\u5ffd\u7565 CHAR \u503c\u5b58\u5728\u7684\u53f3\u586b\u5145,\u5373\u4f7f\u5c06 SQL _MODE \u8bbe\u7f6e\u4e3a PAD_CHAR_TO_FULL_ LENGTH \u4e5f\u4e00\u6837,\u4f8b\u5982:\nmysql> CREATE TABLE t ( a CHAR(10), b VARCHAR(10));\n Query OK, 0 rows affected (0.01 sec)\nmysql> INSERT INTO t SELECT 'a','a';\n Query OK, 1 row affected (0.00 sec)\n Records: 1 Duplicates: 0 Warnings: 0\nmysql> SELECT a=b FROM t\\G;\n *************************** 1. row ***************************\n a=b: 1\n 1 row in set (0.00 sec)\n mysql> SET SQL_MODE='PAD_CHAR_TO_FULL_LENGTH';\n Query OK, 0 rows affected (0.00 sec)\nmysql> SELECT a=b FROM t\\G;\n *************************** 1. row ***************************\n a=b: 1\n 1 row in set (0.00 sec)<\/code><\/pre>\n<\/div>
\n\u6d4b\u8bd5\u524d\u4e86\u89e3\u4e24\u4e2a\u51fd\u6570
\nlength\uff1a\u67e5\u770b\u5b57\u8282\u6570
\nchar_length:\u67e5\u770b\u5b57\u7b26\u6570<\/p>\n
\n- char\u586b\u5145\u7a7a\u683c\u6765\u6ee1\u8db3\u56fa\u5b9a\u957f\u5ea6\uff0c\u4f46\u662f\u5728\u67e5\u8be2\u65f6\u5374\u4f1a\u5f88\u4e0d\u8981\u8138\u5730\u5220\u9664\u5c3e\u90e8\u7684\u7a7a\u683c\uff08\u88c5\u4f5c\u81ea\u5df1\u597d\u50cf\u6ca1\u6709\u6d6a\u8d39\u8fc7\u7a7a\u95f4\u4e00\u6837\uff09\uff0c\u7136\u540e\u4fee\u6539sql_mode\u8ba9\u5176\u73b0\u51fa\u539f\u5f62\n
\nmysql> create table t1(x char(5),y varchar(5));\nQuery OK, 0 rows affected (0.26 sec)<\/code><\/pre>\n<\/li>\n<\/ol>\nchar\u5b585\u4e2a\u5b57\u7b26\uff0c\u800cvarchar\u5b584\u4e2a\u5b57\u7b26<\/h1>\n
mysql> insert into t1 values('\u4f60\u7785\u5565 ','\u4f60\u7785\u5565 ');
\nQuery OK, 1 row affected (0.05 sec)<\/p>\n
mysql> SET sql_mode='';
\nQuery OK, 0 rows affected, 1 warning (0.00 sec)<\/p>\n
\u5728\u68c0\u7d22\u65f6char\u5f88\u4e0d\u8981\u8138\u5730\u5c06\u81ea\u5df1\u6d6a\u8d39\u76842\u4e2a\u5b57\u7b26\u7ed9\u5220\u6389\u4e86\uff0c\u88c5\u7684\u597d\u50cf\u81ea\u5df1\u6ca1\u6d6a\u8d39\u8fc7\u7a7a\u95f4\u4e00\u6837\uff0c\u800cvarchar\u5f88\u8001\u5b9e\uff0c\u5b58\u4e86\u591a\u5c11\uff0c\u5c31\u663e\u793a\u591a\u5c11<\/h1>\n
mysql> select x,char_length(x),y,char_length(y) from t1;
\n+———–+—————-+————+—————-+
\n| x | char_length(x) | y | char_length(y) |
\n+———–+—————-+————+—————-+
\n| \u4f60\u7785\u5565 | 3 | \u4f60\u7785\u5565 | 4 |
\n+———–+—————-+————+—————-+
\n1 row in set (0.00 sec)<\/p>\n
\u7565\u65bd\u5c0f\u8ba1\uff0c\u8ba9char\u73b0\u51fa\u539f\u5f62<\/h1>\n
mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
\nQuery OK, 0 rows affected (0.00 sec)<\/p>\n
\u8fd9\u4e0b\u5b50char\u539f\u5f62\u6bd5\u9732\u4e86……<\/h1>\n
mysql> select x,char_length(x),y,char_length(y) from t1;
\n+————-+—————-+————+—————-+
\n| x | char_length(x) | y | char_length(y) |
\n+————-+—————-+————+—————-+
\n| \u4f60\u7785\u5565 | 5 | \u4f60\u7785\u5565 | 4 |
\n+————-+—————-+————+—————-+
\n1 row in set (0.00 sec)<\/p>\n
char\u7c7b\u578b\uff1a3\u4e2a\u4e2d\u6587\u5b57\u7b26+2\u4e2a\u7a7a\u683c=11Bytes<\/h1>\nvarchar\u7c7b\u578b:3\u4e2a\u4e2d\u6587\u5b57\u7b26+1\u4e2a\u7a7a\u683c=10Bytes<\/h1>\n
mysql> select x,length(x),y,length(y) from t1;
\n+————-+———–+————+———–+
\n| x | length(x) | y | length(y) |
\n+————-+———–+————+———–+
\n| \u4f60\u7785\u5565 | 11 | \u4f60\u7785\u5565 | 10 |
\n+————-+———–+————+———–+
\n1 row in set (0.00 sec)<\/p>\n
\u4e86\u89e3concat<\/code><\/pre>\nmysql> select concat('\u6570\u636e: ',x,'\u957f\u5ea6: ',char_length(x)),concat(y,char_length(y)
\n) from t1;
\n+————————————————+————————–+
\n| concat('\u6570\u636e: ',x,'\u957f\u5ea6: ',char_length(x)) | concat(y,char_length(y)) |
\n+————————————————+————————–+
\n| \u6570\u636e: \u4f60\u7785\u5565 \u957f\u5ea6: 5 | \u4f60\u7785\u5565 4 |
\n+————————————————+————————–+
\n1 row in set (0.00 sec)<\/p>\n
[\u70b9\u51fb\u67e5\u770b\u5173\u4e8esql_mode\u7684\u8be6\u7ec6\u4ecb\u7ecd](https:\/\/egonlin.com\/?p=8193 \"\u70b9\u51fb\u67e5\u770b\u5173\u4e8esql_mode\u7684\u8be6\u7ec6\u4ecb\u7ecd\")\n2. \u867d\u7136 CHAR \u548c VARCHAR \u7684\u5b58\u50a8\u65b9\u5f0f\u4e0d\u592a\u76f8\u540c,\u4f46\u662f\u5bf9\u4e8e\u4e24\u4e2a\u5b57\u7b26\u4e32\u7684\u6bd4\u8f83,\u90fd\u53ea\u6bd4 \u8f83\u5176\u503c,\u5ffd\u7565 CHAR \u503c\u5b58\u5728\u7684\u53f3\u586b\u5145,\u5373\u4f7f\u5c06 SQL _MODE \u8bbe\u7f6e\u4e3a PAD_CHAR_TO_FULL_ LENGTH \u4e5f\u4e00\u6837,,\u4f46\u8fd9\u4e0d\u9002\u7528\u4e8elike<\/code><\/pre>\nValues in CHAR and VARCHAR columns are sorted and compared according to the character set collation assigned to the column.<\/p>\n
All MySQL collations are of type PAD SPACE. This means that all CHAR, VARCHAR, and TEXT values are compared without regard to any trailing spaces. \u201cComparison\u201d in this context does not include the LIKE pattern-matching operator, for which trailing spaces are significant. For example:<\/p>\n
mysql> CREATE TABLE names (myname CHAR(10));
\nQuery OK, 0 rows affected (0.03 sec)<\/p>\n
mysql> INSERT INTO names VALUES ('Monty');
\nQuery OK, 1 row affected (0.00 sec)<\/p>\n
mysql> SELECT myname = 'Monty', myname = 'Monty ' FROM names;
\n+——————+——————–+
\n| myname = 'Monty' | myname = 'Monty ' |
\n+——————+——————–+
\n| 1 | 1 |
\n+——————+——————–+
\n1 row in set (0.00 sec)<\/p>\n
mysql> SELECT myname LIKE 'Monty', myname LIKE 'Monty ' FROM names;
\n+———————+———————–+
\n| myname LIKE 'Monty' | myname LIKE 'Monty ' |
\n+———————+———————–+
\n| 1 | 0 |
\n+———————+———————–+
\n1 row in set (0.00 sec)<\/p>\n
3. \u603b\u7ed3<\/code><\/pre>\nInnoDB\u5b58\u50a8\u5f15\u64ce\uff1a\u5efa\u8bae\u4f7f\u7528VARCHAR\u7c7b\u578b<\/h1>\n
\u5355\u4ece\u6570\u636e\u7c7b\u578b\u7684\u5b9e\u73b0\u673a\u5236\u53bb\u8003\u8651\uff0cchar\u6570\u636e\u7c7b\u578b\u7684\u5904\u7406\u901f\u5ea6\u66f4\u5feb\uff0c\u6709\u65f6\u751a\u81f3\u53ef\u4ee5\u8d85\u51favarchar\u5904\u7406\u901f\u5ea6\u768450%\u3002<\/p>\n
\u4f46\u5bf9\u4e8eInnoDB\u6570\u636e\u8868\uff0c\u5185\u90e8\u7684\u884c\u5b58\u50a8\u683c\u5f0f\u6ca1\u6709\u533a\u5206\u56fa\u5b9a\u957f\u5ea6\u548c\u53ef\u53d8\u957f\u5ea6\u5217\uff08\u6240\u6709\u6570\u636e\u884c\u90fd\u4f7f\u7528\u6307\u5411\u6570\u636e\u5217\u503c\u7684\u5934\u6307\u9488\uff09\uff0c\u56e0\u6b64\u5728\u672c\u8d28\u4e0a\uff0c\u4f7f\u7528\u56fa\u5b9a\u957f\u5ea6\u7684CHAR\u5217\u4e0d\u4e00\u5b9a\u6bd4\u4f7f\u7528\u53ef\u53d8\u957f\u5ea6VARCHAR\u5217\u6027\u80fd\u8981\u597d\u3002\u56e0\u800c\uff0c\u4e3b\u8981\u7684\u6027\u80fd\u56e0\u7d20\u662f\u6570\u636e\u884c\u4f7f\u7528\u7684\u5b58\u50a8\u603b\u91cf\u3002\u7531\u4e8eCHAR\u5e73\u5747\u5360\u7528\u7684\u7a7a\u95f4\u591a\u4e8eVARCHAR\uff0c\u56e0\u6b64\u4f7f\u7528VARCHAR\u6765\u6700\u5c0f\u5316\u9700\u8981\u5904\u7406\u7684\u6570\u636e\u884c\u7684\u5b58\u50a8\u603b\u91cf\u548c\u78c1\u76d8I\/O\u662f\u6bd4\u8f83\u597d\u7684\u3002<\/p>\n
\u5176\u4ed6\u5b57\u7b26\u4e32\u7cfb\u5217\uff08\u6548\u7387\uff1achar>varchar>text\uff09<\/h1>\n
TEXT\u7cfb\u5217 TINYTEXT TEXT MEDIUMTEXT LONGTEXT
\nBLOB \u7cfb\u5217 TINYBLOB BLOB MEDIUMBLOB LONGBLOB
\nBINARY\u7cfb\u5217 BINARY VARBINARY<\/p>\n
text\uff1atext\u6570\u636e\u7c7b\u578b\u7528\u4e8e\u4fdd\u5b58\u53d8\u957f\u7684\u5927\u5b57\u7b26\u4e32\uff0c\u53ef\u4ee5\u7ec4\u591a\u523065535 (216 \u2212 1)\u4e2a\u5b57\u7b26\u3002
\nmediumtext\uff1aA TEXT column with a maximum length of 16,777,215 (2<\/strong>24 \u2212 1) characters.
\nlongtext\uff1aA TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 \u2212 1) characters.<\/p>\n\n### 5.5 \u679a\u4e3e\u7c7b\u578b\u4e0e\u96c6\u5408\u7c7b\u578b\n\u5b57\u6bb5\u7684\u503c\u53ea\u80fd\u5728\u7ed9\u5b9a\u8303\u56f4\u4e2d\u9009\u62e9\uff0c\u5982\u5355\u9009\u6846\uff0c\u591a\u9009\u6846\nenum \u5355\u9009 \u53ea\u80fd\u5728\u7ed9\u5b9a\u7684\u8303\u56f4\u5185\u9009\u4e00\u4e2a\u503c\uff0c\u5982\u6027\u522b sex \u7537male\/\u5973female\nset \u591a\u9009 \u5728\u7ed9\u5b9a\u7684\u8303\u56f4\u5185\u53ef\u4ee5\u9009\u62e9\u4e00\u4e2a\u6216\u4e00\u4e2a\u4ee5\u4e0a\u7684\u503c\uff08\u7231\u597d1,\u7231\u597d2,\u7231\u597d3...\uff09<\/code><\/pre>\n \u679a\u4e3e\u7c7b\u578b\uff08enum\uff09\n An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)\n \u793a\u4f8b\uff1a\n CREATE TABLE shirts (\n name VARCHAR(40),\n size ENUM('x-small', 'small', 'medium', 'large', 'x-large')\n );\n INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');\n\n \u96c6\u5408\u7c7b\u578b\uff08set\uff09\n A SET column can have a maximum of 64 distinct members.\n \u793a\u4f8b\uff1a\n CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));\n INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');<\/code><\/pre>\n\u9a8c\u8bc1<\/code><\/pre>\nMariaDB [db1]> create table consumer(
\n-> name varchar(50),
\n-> sex enum('male','female'),
\n-> level enum('vip1','vip2','vip3','vip4','vip5'), #\u5728\u6307\u5b9a\u8303\u56f4\u5185\uff0c\u591a\u9009\u4e00
\n-> hobby set('play','music','read','study') #\u5728\u6307\u5b9a\u8303\u56f4\u5185\uff0c\u591a\u9009\u591a
\n-> );<\/p>\n
MariaDB [db1]> insert into consumer values
\n-> ('egon','male','vip5','read,study'),
\n-> ('alex','female','vip1','girl');<\/p>\n
MariaDB [db1]> select * from consumer;
\n+——+——–+——-+————+
\n| name | sex | level | hobby |
\n+——+——–+——-+————+
\n| egon | male | vip5 | read,study |
\n| alex | female | vip1 | |
\n+——+——–+——-+————+<\/p>\n
\n## \u516d\u3001\u8868\u5b8c\u6574\u6027\u7ea6\u675f\n\n### 1\u3001\u4ecb\u7ecd\n\n\u7ea6\u675f\u6761\u4ef6\u4e0e\u6570\u636e\u7c7b\u578b\u7684\u5bbd\u5ea6\u4e00\u6837\uff0c\u90fd\u662f\u53ef\u9009\u53c2\u6570\n\n\u4f5c\u7528\uff1a\u7528\u4e8e\u4fdd\u8bc1\u6570\u636e\u7684\u5b8c\u6574\u6027\u548c\u4e00\u81f4\u6027 \u4e3b\u8981\u5206\u4e3a\uff1a\n\n```yaml\nPRIMARY KEY (PK) \u6807\u8bc6\u8be5\u5b57\u6bb5\u4e3a\u8be5\u8868\u7684\u4e3b\u952e\uff0c\u53ef\u4ee5\u552f\u4e00\u7684\u6807\u8bc6\u8bb0\u5f55\nFOREIGN KEY (FK) \u6807\u8bc6\u8be5\u5b57\u6bb5\u4e3a\u8be5\u8868\u7684\u5916\u952e\nNOT NULL \u6807\u8bc6\u8be5\u5b57\u6bb5\u4e0d\u80fd\u4e3a\u7a7a\nUNIQUE KEY (UK) \u6807\u8bc6\u8be5\u5b57\u6bb5\u7684\u503c\u662f\u552f\u4e00\u7684\nAUTO_INCREMENT \u6807\u8bc6\u8be5\u5b57\u6bb5\u7684\u503c\u81ea\u52a8\u589e\u957f\uff08\u6574\u6570\u7c7b\u578b\uff0c\u800c\u4e14\u4e3a\u4e3b\u952e\uff09\nDEFAULT \u4e3a\u8be5\u5b57\u6bb5\u8bbe\u7f6e\u9ed8\u8ba4\u503c\n\nUNSIGNED \u65e0\u7b26\u53f7\nZEROFILL \u4f7f\u75280\u586b\u5145<\/code><\/pre>\n\u8bf4\u660e<\/p>\n
1. \u662f\u5426\u5141\u8bb8\u4e3a\u7a7a\uff0c\u9ed8\u8ba4NULL\uff0c\u53ef\u8bbe\u7f6eNOT NULL\uff0c\u5b57\u6bb5\u4e0d\u5141\u8bb8\u4e3a\u7a7a\uff0c\u5fc5\u987b\u8d4b\u503c\n2. \u5b57\u6bb5\u662f\u5426\u6709\u9ed8\u8ba4\u503c\uff0c\u7f3a\u7701\u7684\u9ed8\u8ba4\u503c\u662fNULL\uff0c\u5982\u679c\u63d2\u5165\u8bb0\u5f55\u65f6\u4e0d\u7ed9\u5b57\u6bb5\u8d4b\u503c\uff0c\u6b64\u5b57\u6bb5\u4f7f\u7528\u9ed8\u8ba4\u503c\nsex enum('male','female') not null default 'male'\nage int unsigned NOT NULL default 20 \u5fc5\u987b\u4e3a\u6b63\u503c\uff08\u65e0\u7b26\u53f7\uff09 \u4e0d\u5141\u8bb8\u4e3a\u7a7a \u9ed8\u8ba4\u662f20\n3. \u662f\u5426\u662fkey\n\u4e3b\u952e primary key\n\u5916\u952e foreign key\n\u7d22\u5f15 (index,unique...)<\/code><\/pre>\n2\u3001not null\u4e0edefault<\/h3>\n
\u662f\u5426\u53ef\u7a7a\uff0cnull\u8868\u793a\u7a7a\uff0c\u975e\u5b57\u7b26\u4e32 not null – \u4e0d\u53ef\u7a7a null – \u53ef\u7a7a<\/p>\n
\u9ed8\u8ba4\u503c\uff0c\u521b\u5efa\u5217\u65f6\u53ef\u4ee5\u6307\u5b9a\u9ed8\u8ba4\u503c\uff0c\u5f53\u63d2\u5165\u6570\u636e\u65f6\u5982\u679c\u672a\u4e3b\u52a8\u8bbe\u7f6e\uff0c\u5219\u81ea\u52a8\u6dfb\u52a0\u9ed8\u8ba4\u503c create table tb1( nid int not null defalut 2, num int not null )<\/p>\n
\u9a8c\u8bc1<\/p>\n
==================not null====================\nmysql> create table t1(id int); #id\u5b57\u6bb5\u9ed8\u8ba4\u53ef\u4ee5\u63d2\u5165\u7a7a\nmysql> desc t1;\n+-------+---------+------+-----+---------+-------+\n| Field | Type | Null | Key | Default | Extra |\n+-------+---------+------+-----+---------+-------+\n| id | int(11) | YES | | NULL | |\n+-------+---------+------+-----+---------+-------+\nmysql> insert into t1 values(); #\u53ef\u4ee5\u63d2\u5165\u7a7a\n\nmysql> create table t2(id int not null); #\u8bbe\u7f6e\u5b57\u6bb5id\u4e0d\u4e3a\u7a7a\nmysql> desc t2;\n+-------+---------+------+-----+---------+-------+\n| Field | Type | Null | Key | Default | Extra |\n+-------+---------+------+-----+---------+-------+\n| id | int(11) | NO | | NULL | |\n+-------+---------+------+-----+---------+-------+\nmysql> insert into t2 values(); #\u4e0d\u80fd\u63d2\u5165\u7a7a\nERROR 1364 (HY000): Field 'id' doesn't have a default value\n\n==================default====================\n#\u8bbe\u7f6eid\u5b57\u6bb5\u6709\u9ed8\u8ba4\u503c\u540e\uff0c\u5219\u65e0\u8bbaid\u5b57\u6bb5\u662fnull\u8fd8\u662fnot null\uff0c\u90fd\u53ef\u4ee5\u63d2\u5165\u7a7a\uff0c\u63d2\u5165\u7a7a\u9ed8\u8ba4\u586b\u5165default\u6307\u5b9a\u7684\u9ed8\u8ba4\u503c\nmysql> create table t3(id int default 1);\nmysql> alter table t3 modify id int not null default 1;\n\n==================\u7efc\u5408\u7ec3\u4e60====================\nmysql> create table student(\n -> name varchar(20) not null,\n -> age int(3) unsigned not null default 18,\n -> sex enum('male','female') default 'male',\n -> hobby set('play','study','read','music') default 'play,music'\n -> );\nmysql> desc student;\n+-------+------------------------------------+------+-----+------------+-------+\n| Field | Type | Null | Key | Default | Extra |\n+-------+------------------------------------+------+-----+------------+-------+\n| name | varchar(20) | NO | | NULL | |\n| age | int(3) unsigned | NO | | 18 | |\n| sex | enum('male','female') | YES | | male | |\n| hobby | set('play','study','read','music') | YES | | play,music | |\n+-------+------------------------------------+------+-----+------------+-------+\nmysql> insert into student(name) values('egon');\nmysql> select * from student;\n+------+-----+------+------------+\n| name | age | sex | hobby |\n+------+-----+------+------------+\n| egon | 18 | male | play,music |\n+------+-----+------+------------+<\/code><\/pre>\n3\u3001unique<\/h3>\n============\u8bbe\u7f6e\u552f\u4e00\u7ea6\u675f UNIQUE===============\n\u65b9\u6cd5\u4e00\uff1a\ncreate table department1(\nid int,\nname varchar(20) unique,\ncomment varchar(100)\n);\n\n\u65b9\u6cd5\u4e8c\uff1a\ncreate table department2(\nid int,\nname varchar(20),\ncomment varchar(100),\nconstraint uk_name unique(name)\n);\n\nmysql> insert into department1 values(1,'IT','\u6280\u672f');\nQuery OK, 1 row affected (0.00 sec)\nmysql> insert into department1 values(1,'IT','\u6280\u672f');\nERROR 1062 (23000): Duplicate entry 'IT' for key 'name'\n\n# not null+unique\u7684\u5316\u5b66\u53cd\u5e94\nmysql> create table t1(id int not null unique);\nQuery OK, 0 rows affected (0.02 sec)\n\nmysql> desc t1;\n+-------+---------+------+-----+---------+-------+\n| Field | Type | Null | Key | Default | Extra |\n+-------+---------+------+-----+---------+-------+\n| id | int(11) | NO | PRI | NULL | |\n+-------+---------+------+-----+---------+-------+\n1 row in set (0.00 sec)\n\n# \u8054\u5408\u552f\u4e00\ncreate table service(\nid int primary key auto_increment,\nname varchar(20),\nhost varchar(15) not null,\nport int not null,\nunique(host,port) #\u8054\u5408\u552f\u4e00\n);\n\nmysql> insert into service values\n -> (1,'nginx','192.168.0.10',80),\n -> (2,'haproxy','192.168.0.20',80),\n -> (3,'mysql','192.168.0.30',3306)\n -> ;\nQuery OK, 3 rows affected (0.01 sec)\nRecords: 3 Duplicates: 0 Warnings: 0\n\nmysql> insert into service(name,host,port) values('nginx','192.168.0.10',80);\nERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'<\/code><\/pre>\n4\u3001primary key<\/h3>\n
\u4ece\u7ea6\u675f\u89d2\u5ea6\u770bprimary key\u5b57\u6bb5\u7684\u503c\u4e0d\u4e3a\u7a7a\u4e14\u552f\u4e00\uff0c\u90a3\u6211\u4eec\u76f4\u63a5\u4f7f\u7528not null+unique\u4e0d\u5c31\u53ef\u4ee5\u4e86\u5417\uff0c\u8981\u5b83\u5e72\u4ec0\u4e48\uff1f<\/p>\n
\u4e3b\u952eprimary key\u662finnodb\u5b58\u50a8\u5f15\u64ce\u7ec4\u7ec7\u6570\u636e\u7684\u4f9d\u636e\uff0cinnodb\u79f0\u4e4b\u4e3a\u7d22\u5f15\u7ec4\u7ec7\u8868\uff0c\u4e00\u5f20\u8868\u4e2d\u5fc5\u987b\u6709\u4e14\u53ea\u6709\u4e00\u4e2a\u4e3b\u952e\u3002<\/p>\n
\u4e00\u4e2a\u8868\u4e2d\u53ef\u4ee5\uff1a<\/p>\n
\u5355\u5217\u505a\u4e3b\u952e \u591a\u5217\u505a\u4e3b\u952e\uff08\u590d\u5408\u4e3b\u952e\uff09<\/p>\n
# \u5355\u5217\u4e3b\u952e\n============\u5355\u5217\u505a\u4e3b\u952e===============\n#\u65b9\u6cd5\u4e00\uff1anot null+unique\ncreate table department1(\nid int not null unique, #\u4e3b\u952e\nname varchar(20) not null unique,\ncomment varchar(100)\n);\n\nmysql> desc department1;\n+---------+--------------+------+-----+---------+-------+\n| Field | Type | Null | Key | Default | Extra |\n+---------+--------------+------+-----+---------+-------+\n| id | int(11) | NO | PRI | NULL | |\n| name | varchar(20) | NO | UNI | NULL | |\n| comment | varchar(100) | YES | | NULL | |\n+---------+--------------+------+-----+---------+-------+\nrows in set (0.01 sec)\n\n#\u65b9\u6cd5\u4e8c\uff1a\u5728\u67d0\u4e00\u4e2a\u5b57\u6bb5\u540e\u7528primary key\ncreate table department2(\nid int primary key, #\u4e3b\u952e\nname varchar(20),\ncomment varchar(100)\n);\n\nmysql> desc department2;\n+---------+--------------+------+-----+---------+-------+\n| Field | Type | Null | Key | Default | Extra |\n+---------+--------------+------+-----+---------+-------+\n| id | int(11) | NO | PRI | NULL | |\n| name | varchar(20) | YES | | NULL | |\n| comment | varchar(100) | YES | | NULL | |\n+---------+--------------+------+-----+---------+-------+\nrows in set (0.00 sec)\n\n#\u65b9\u6cd5\u4e09\uff1a\u5728\u6240\u6709\u5b57\u6bb5\u540e\u5355\u72ec\u5b9a\u4e49primary key\ncreate table department3(\nid int,\nname varchar(20),\ncomment varchar(100),\nconstraint pk_name primary key(id); #\u521b\u5efa\u4e3b\u952e\u5e76\u4e3a\u5176\u547d\u540dpk_name\n\nmysql> desc department3;\n+---------+--------------+------+-----+---------+-------+\n| Field | Type | Null | Key | Default | Extra |\n+---------+--------------+------+-----+---------+-------+\n| id | int(11) | NO | PRI | NULL | |\n| name | varchar(20) | YES | | NULL | |\n| comment | varchar(100) | YES | | NULL | |\n+---------+--------------+------+-----+---------+-------+\nrows in set (0.01 sec)\n\n# \u591a\u5217\u4e3b\u952e\n==================\u591a\u5217\u505a\u4e3b\u952e================\ncreate table service(\nip varchar(15),\nport char(5),\nservice_name varchar(10) not null,\nprimary key(ip,port)\n);\n\nmysql> desc service;\n+--------------+-------------+------+-----+---------+-------+\n| Field | Type | Null | Key | Default | Extra |\n+--------------+-------------+------+-----+---------+-------+\n| ip | varchar(15) | NO | PRI | NULL | |\n| port | char(5) | NO | PRI | NULL | |\n| service_name | varchar(10) | NO | | NULL | |\n+--------------+-------------+------+-----+---------+-------+\n3 rows in set (0.00 sec)\n\nmysql> insert into service values\n -> ('172.16.45.10','3306','mysqld'),\n -> ('172.16.45.11','3306','mariadb')\n -> ;\nQuery OK, 2 rows affected (0.00 sec)\nRecords: 2 Duplicates: 0 Warnings: 0\n\nmysql> insert into service values ('172.16.45.10','3306','nginx');\nERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'<\/code><\/pre>\n5\u3001auto_increment<\/h3>\n\u7ea6\u675f\u5b57\u6bb5\u4e3a\u81ea\u52a8\u589e\u957f\uff0c\u88ab\u7ea6\u675f\u7684\u5b57\u6bb5\u5fc5\u987b\u540c\u65f6\u88abkey\u7ea6\u675f\n#\u4e0d\u6307\u5b9aid\uff0c\u5219\u81ea\u52a8\u589e\u957f\ncreate table student(\nid int primary key auto_increment,\nname varchar(20),\nsex enum('male','female') default 'male'\n);\n\nmysql> desc student;\n+-------+-----------------------+------+-----+---------+----------------+\n| Field | Type | Null | Key | Default | Extra |\n+-------+-----------------------+------+-----+---------+----------------+\n| id | int(11) | NO | PRI | NULL | auto_increment |\n| name | varchar(20) | YES | | NULL | |\n| sex | enum('male','female') | YES | | male | |\n+-------+-----------------------+------+-----+---------+----------------+\nmysql> insert into student(name) values\n -> ('egon'),\n -> ('alex')\n -> ;\n\nmysql> select * from student;\n+----+------+------+\n| id | name | sex |\n+----+------+------+\n| 1 | egon | male |\n| 2 | alex | male |\n+----+------+------+\n\n#\u4e5f\u53ef\u4ee5\u6307\u5b9aid\nmysql> insert into student values(4,'asb','female');\nQuery OK, 1 row affected (0.00 sec)\n\nmysql> insert into student values(7,'wsb','female');\nQuery OK, 1 row affected (0.00 sec)\n\nmysql> select * from student;\n+----+------+--------+\n| id | name | sex |\n+----+------+--------+\n| 1 | egon | male |\n| 2 | alex | male |\n| 4 | asb | female |\n| 7 | wsb | female |\n+----+------+--------+\n\n#\u5bf9\u4e8e\u81ea\u589e\u7684\u5b57\u6bb5\uff0c\u5728\u7528delete\u5220\u9664\u540e\uff0c\u518d\u63d2\u5165\u503c\uff0c\u8be5\u5b57\u6bb5\u4ecd\u6309\u7167\u5220\u9664\u524d\u7684\u4f4d\u7f6e\u7ee7\u7eed\u589e\u957f\nmysql> delete from student;\nQuery OK, 4 rows affected (0.00 sec)\n\nmysql> select * from student;\nEmpty set (0.00 sec)\n\nmysql> insert into student(name) values('ysb');\nmysql> select * from student;\n+----+------+------+\n| id | name | sex |\n+----+------+------+\n| 8 | ysb | male |\n+----+------+------+\n\n#\u5e94\u8be5\u7528truncate\u6e05\u7a7a\u8868\uff0c\u6bd4\u8d77delete\u4e00\u6761\u4e00\u6761\u5730\u5220\u9664\u8bb0\u5f55\uff0ctruncate\u662f\u76f4\u63a5\u6e05\u7a7a\u8868\uff0c\u5728\u5220\u9664\u5927\u8868\u65f6\u7528\u5b83\nmysql> truncate student;\nQuery OK, 0 rows affected (0.01 sec)\n\nmysql> insert into student(name) values('egon');\nQuery OK, 1 row affected (0.01 sec)\n\nmysql> select * from student;\n+----+------+------+\n| id | name | sex |\n+----+------+------+\n| 1 | egon | male |\n+----+------+------+\n1 row in set (0.00 sec)\n\n# \u4e86\u89e3\u77e5\u8bc6\n\u6b65\u957f:auto_increment_increment,\u8d77\u59cb\u504f\u79fb\u91cf:auto_increment_offset\n#\u5728\u521b\u5efa\u5b8c\u8868\u540e\uff0c\u4fee\u6539\u81ea\u589e\u5b57\u6bb5\u7684\u8d77\u59cb\u503c\nmysql> create table student(\n -> id int primary key auto_increment,\n -> name varchar(20),\n -> sex enum('male','female') default 'male'\n -> );\n\nmysql> alter table student auto_increment=3;\n\nmysql> show create table student;\n.......\nENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8\n\nmysql> insert into student(name) values('egon');\nQuery OK, 1 row affected (0.01 sec)\n\nmysql> select * from student;\n+----+------+------+\n| id | name | sex |\n+----+------+------+\n| 3 | egon | male |\n+----+------+------+\nrow in set (0.00 sec)\n\nmysql> show create table student;\n.......\nENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8\n\n#\u4e5f\u53ef\u4ee5\u521b\u5efa\u8868\u65f6\u6307\u5b9aauto_increment\u7684\u521d\u59cb\u503c\uff0c\u6ce8\u610f\u521d\u59cb\u503c\u7684\u8bbe\u7f6e\u4e3a\u8868\u9009\u9879\uff0c\u5e94\u8be5\u653e\u5230\u62ec\u53f7\u5916\ncreate table student(\nid int primary key auto_increment,\nname varchar(20),\nsex enum('male','female') default 'male'\n)auto_increment=3;\n\n#\u8bbe\u7f6e\u6b65\u957f\nsqlserver\uff1a\u81ea\u589e\u6b65\u957f\n \u57fa\u4e8e\u8868\u7ea7\u522b\n create table t1\uff08\n id int\u3002\u3002\u3002\n \uff09engine=innodb,auto_increment=2 \u6b65\u957f=2 default charset=utf8\n\nmysql\u81ea\u589e\u7684\u6b65\u957f\uff1a\n show session variables like 'auto_inc%';\n\n #\u57fa\u4e8e\u4f1a\u8bdd\u7ea7\u522b\n set session auth_increment_increment=2 #\u4fee\u6539\u4f1a\u8bdd\u7ea7\u522b\u7684\u6b65\u957f\n\n #\u57fa\u4e8e\u5168\u5c40\u7ea7\u522b\u7684\n set global auth_increment_increment=2 #\u4fee\u6539\u5168\u5c40\u7ea7\u522b\u7684\u6b65\u957f\uff08\u6240\u6709\u4f1a\u8bdd\u90fd\u751f\u6548\uff09\n\n#\uff01\uff01\uff01\u6ce8\u610f\u4e86\u6ce8\u610f\u4e86\u6ce8\u610f\u4e86\uff01\uff01\uff01\nIf the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored. \n\u7ffb\u8bd1\uff1a\u5982\u679cauto_increment_offset\u7684\u503c\u5927\u4e8eauto_increment_increment\u7684\u503c\uff0c\u5219auto_increment_offset\u7684\u503c\u4f1a\u88ab\u5ffd\u7565 \uff0c\u8fd9\u76f8\u5f53\u4e8e\u7b2c\u4e00\u6b65\u6b65\u5b50\u5c31\u8fc8\u5927\u4e86\uff0c\u626f\u7740\u4e86\u86cb\n\u6bd4\u5982\uff1a\u8bbe\u7f6eauto_increment_offset=3\uff0cauto_increment_increment=2\n\nmysql> set global auto_increment_increment=5;\nQuery OK, 0 rows affected (0.00 sec)\n\nmysql> set global auto_increment_offset=3;\nQuery OK, 0 rows affected (0.00 sec)\n\nmysql> show variables like 'auto_incre%'; #\u9700\u8981\u9000\u51fa\u91cd\u65b0\u767b\u5f55\n+--------------------------+-------+\n| Variable_name | Value |\n+--------------------------+-------+\n| auto_increment_increment | 1 |\n| auto_increment_offset | 1 |\n+--------------------------+-------+\n\ncreate table student(\nid int primary key auto_increment,\nname varchar(20),\nsex enum('male','female') default 'male'\n);\n\nmysql> insert into student(name) values('egon1'),('egon2'),('egon3');\nmysql> select * from student;\n+----+-------+------+\n| id | name | sex |\n+----+-------+------+\n| 3 | egon1 | male |\n| 8 | egon2 | male |\n| 13 | egon3 | male |\n+----+-------+------+<\/code><\/pre>\n6\u3001\u5916\u952e\uff08foreign key\uff09<\/h3>\n1.\u5feb\u901f\u7406\u89e3foreign key<\/h4>\n
\u5458\u5de5\u4fe1\u606f\u8868\u6709\u4e09\u4e2a\u5b57\u6bb5\uff1a\u5de5\u53f7 \u59d3\u540d \u90e8\u95e8<\/p>\n
\u516c\u53f8\u67093\u4e2a\u90e8\u95e8\uff0c\u4f46\u662f\u67091\u4e2a\u4ebf\u7684\u5458\u5de5\uff0c\u90a3\u610f\u5473\u7740\u90e8\u95e8\u8fd9\u4e2a\u5b57\u6bb5\u9700\u8981\u91cd\u590d\u5b58\u50a8\uff0c\u90e8\u95e8\u540d\u5b57\u8d8a\u957f\uff0c\u8d8a\u6d6a\u8d39<\/p>\n
\u89e3\u51b3\u65b9\u6cd5\uff1a<\/p>\n
\u6211\u4eec\u5b8c\u5168\u53ef\u4ee5\u5b9a\u4e49\u4e00\u4e2a\u90e8\u95e8\u8868<\/p>\n
\u7136\u540e\u8ba9\u5458\u5de5\u4fe1\u606f\u8868\u5173\u8054\u8be5\u8868\uff0c\u5982\u4f55\u5173\u8054\uff0c\u5373foreign key<\/p>\n
\u793a\u8303<\/p>\n
#\u8868\u7c7b\u578b\u5fc5\u987b\u662finnodb\u5b58\u50a8\u5f15\u64ce\uff0c\u4e14\u88ab\u5173\u8054\u7684\u5b57\u6bb5\uff0c\u5373references\u6307\u5b9a\u7684\u53e6\u5916\u4e00\u4e2a\u8868\u7684\u5b57\u6bb5\uff0c\u5fc5\u987b\u4fdd\u8bc1\u552f\u4e00\ncreate table department(\nid int primary key,\nname varchar(20) not null\n)engine=innodb;\n\n#dpt_id\u5916\u952e\uff0c\u5173\u8054\u7236\u8868\uff08department\u4e3b\u952eid\uff09\uff0c\u540c\u6b65\u66f4\u65b0\uff0c\u540c\u6b65\u5220\u9664\ncreate table employee(\nid int primary key,\nname varchar(20) not null,\ndpt_id int,\nconstraint fk_name foreign key(dpt_id)\nreferences department(id)\non delete cascade\non update cascade \n)engine=innodb;\n\n#\u5148\u5f80\u7236\u8868department\u4e2d\u63d2\u5165\u8bb0\u5f55\ninsert into department values\n(1,'\u6b27\u5fb7\u535a\u7231\u6280\u672f\u6709\u9650\u4e8b\u4e1a\u90e8'),\n(2,'\u827e\u5229\u514b\u65af\u4eba\u529b\u8d44\u6e90\u90e8'),\n(3,'\u9500\u552e\u90e8');\n\n#\u518d\u5f80\u5b50\u8868employee\u4e2d\u63d2\u5165\u8bb0\u5f55\ninsert into employee values\n(1,'egon',1),\n(2,'alex1',2),\n(3,'alex2',2),\n(4,'alex3',2),\n(5,'\u674e\u5766\u514b',3),\n(6,'\u5218\u98de\u673a',3),\n(7,'\u5f20\u706b\u7bad',3),\n(8,'\u6797\u5b50\u5f39',3),\n(9,'\u52a0\u7279\u6797',3)\n;\n\n#\u5220\u7236\u8868department\uff0c\u5b50\u8868employee\u4e2d\u5bf9\u5e94\u7684\u8bb0\u5f55\u8ddf\u7740\u5220\nmysql> delete from department where id=3;\nmysql> select * from employee;\n+----+-------+--------+\n| id | name | dpt_id |\n+----+-------+--------+\n| 1 | egon | 1 |\n| 2 | alex1 | 2 |\n| 3 | alex2 | 2 |\n| 4 | alex3 | 2 |\n+----+-------+--------+\n\n#\u66f4\u65b0\u7236\u8868department\uff0c\u5b50\u8868employee\u4e2d\u5bf9\u5e94\u7684\u8bb0\u5f55\u8ddf\u7740\u6539\nmysql> update department set id=22222 where id=2;\nmysql> select * from employee;\n+----+-------+--------+\n| id | name | dpt_id |\n+----+-------+--------+\n| 1 | egon | 1 |\n| 3 | alex2 | 22222 |\n| 4 | alex3 | 22222 |\n| 5 | alex1 | 22222 |\n+----+-------+--------+<\/code><\/pre>\n2.\u5982\u4f55\u627e\u51fa\u4e24\u5f20\u8868\u4e4b\u95f4\u7684\u5173\u7cfb<\/h4>\n\u5206\u6790\u6b65\u9aa4\uff1a\n#1\u3001\u5148\u7ad9\u5728\u5de6\u8868\u7684\u89d2\u5ea6\u53bb\u627e\n\u662f\u5426\u5de6\u8868\u7684\u591a\u6761\u8bb0\u5f55\u53ef\u4ee5\u5bf9\u5e94\u53f3\u8868\u7684\u4e00\u6761\u8bb0\u5f55\uff0c\u5982\u679c\u662f\uff0c\u5219\u8bc1\u660e\u5de6\u8868\u7684\u4e00\u4e2a\u5b57\u6bb5foreign key \u53f3\u8868\u4e00\u4e2a\u5b57\u6bb5\uff08\u901a\u5e38\u662fid\uff09\n\n#2\u3001\u518d\u7ad9\u5728\u53f3\u8868\u7684\u89d2\u5ea6\u53bb\u627e\n\u662f\u5426\u53f3\u8868\u7684\u591a\u6761\u8bb0\u5f55\u53ef\u4ee5\u5bf9\u5e94\u5de6\u8868\u7684\u4e00\u6761\u8bb0\u5f55\uff0c\u5982\u679c\u662f\uff0c\u5219\u8bc1\u660e\u53f3\u8868\u7684\u4e00\u4e2a\u5b57\u6bb5foreign key \u5de6\u8868\u4e00\u4e2a\u5b57\u6bb5\uff08\u901a\u5e38\u662fid\uff09\n\n#3\u3001\u603b\u7ed3\uff1a\n#\u591a\u5bf9\u4e00\uff1a\n\u5982\u679c\u53ea\u6709\u6b65\u9aa41\u6210\u7acb\uff0c\u5219\u662f\u5de6\u8868\u591a\u5bf9\u4e00\u53f3\u8868\n\u5982\u679c\u53ea\u6709\u6b65\u9aa42\u6210\u7acb\uff0c\u5219\u662f\u53f3\u8868\u591a\u5bf9\u4e00\u5de6\u8868\n\n#\u591a\u5bf9\u591a\n\u5982\u679c\u6b65\u9aa41\u548c2\u540c\u65f6\u6210\u7acb\uff0c\u5219\u8bc1\u660e\u8fd9\u4e24\u5f20\u8868\u65f6\u4e00\u4e2a\u53cc\u5411\u7684\u591a\u5bf9\u4e00\uff0c\u5373\u591a\u5bf9\u591a,\u9700\u8981\u5b9a\u4e49\u4e00\u4e2a\u8fd9\u4e24\u5f20\u8868\u7684\u5173\u7cfb\u8868\u6765\u4e13\u95e8\u5b58\u653e\u4e8c\u8005\u7684\u5173\u7cfb\n\n#\u4e00\u5bf9\u4e00:\n\u5982\u679c1\u548c2\u90fd\u4e0d\u6210\u7acb\uff0c\u800c\u662f\u5de6\u8868\u7684\u4e00\u6761\u8bb0\u5f55\u552f\u4e00\u5bf9\u5e94\u53f3\u8868\u7684\u4e00\u6761\u8bb0\u5f55\uff0c\u53cd\u4e4b\u4ea6\u7136\u3002\u8fd9\u79cd\u60c5\u51b5\u5f88\u7b80\u5355\uff0c\u5c31\u662f\u5728\u5de6\u8868foreign key\u53f3\u8868\u7684\u57fa\u7840\u4e0a\uff0c\u5c06\u5de6\u8868\u7684\u5916\u952e\u5b57\u6bb5\u8bbe\u7f6e\u6210unique\u5373\u53ef<\/code><\/pre>\n3.\u5efa\u7acb\u8868\u4e4b\u95f4\u7684\u5173\u7cfb<\/h4>\n#\u4e00\u5bf9\u591a\u6216\u79f0\u4e3a\u591a\u5bf9\u4e00\n\u4e09\u5f20\u8868\uff1a\u51fa\u7248\u793e\uff0c\u4f5c\u8005\u4fe1\u606f\uff0c\u4e66\n\n\u4e00\u5bf9\u591a\uff08\u6216\u591a\u5bf9\u4e00\uff09\uff1a\u4e00\u4e2a\u51fa\u7248\u793e\u53ef\u4ee5\u51fa\u7248\u591a\u672c\u4e66\n\n\u5173\u8054\u65b9\u5f0f\uff1aforeign key\n=====================\u591a\u5bf9\u4e00=====================\ncreate table press(\nid int primary key auto_increment,\nname varchar(20)\n);\n\ncreate table book(\nid int primary key auto_increment,\nname varchar(20),\npress_id int not null,\nforeign key(press_id) references press(id)\non delete cascade\non update cascade\n);\n\ninsert into press(name) values\n('\u5317\u4eac\u5de5\u4e1a\u5730\u96f7\u51fa\u7248\u793e'),\n('\u4eba\u6c11\u97f3\u4e50\u4e0d\u597d\u542c\u51fa\u7248\u793e'),\n('\u77e5\u8bc6\u4ea7\u6743\u6ca1\u6709\u7528\u51fa\u7248\u793e')\n;\n\ninsert into book(name,press_id) values\n('\u4e5d\u9633\u795e\u529f',1),\n('\u4e5d\u9634\u771f\u7ecf',2),\n('\u4e5d\u9634\u767d\u9aa8\u722a',2),\n('\u72ec\u5b64\u4e5d\u5251',3),\n('\u964d\u9f99\u5341\u5df4\u638c',2),\n('\u8475\u82b1\u5b9d\u5178',3)\n;\n\u5176\u4ed6\u4f8b\u5b50\n\u4e00\u592b\u591a\u59bb\u5236\n\n#\u59bb\u5b50\u8868\u7684\u4e08\u592bid\u5916\u952e\u5230\u4e08\u592b\u8868\u7684id\n#\u591a\u5bf9\u591a\n\u4e09\u5f20\u8868\uff1a\u51fa\u7248\u793e\uff0c\u4f5c\u8005\u4fe1\u606f\uff0c\u4e66\n\n\u591a\u5bf9\u591a\uff1a\u4e00\u4e2a\u4f5c\u8005\u53ef\u4ee5\u5199\u591a\u672c\u4e66\uff0c\u4e00\u672c\u4e66\u4e5f\u53ef\u4ee5\u6709\u591a\u4e2a\u4f5c\u8005\uff0c\u53cc\u5411\u7684\u4e00\u5bf9\u591a\uff0c\u5373\u591a\u5bf9\u591a\n\u3000\u3000\n\u5173\u8054\u65b9\u5f0f\uff1aforeign key+\u4e00\u5f20\u65b0\u7684\u8868\n=====================\u591a\u5bf9\u591a=====================\ncreate table author(\nid int primary key auto_increment,\nname varchar(20)\n);\n\n#\u8fd9\u5f20\u8868\u5c31\u5b58\u653e\u4f5c\u8005\u8868\u4e0e\u4e66\u8868\u7684\u5173\u7cfb\uff0c\u5373\u67e5\u8be2\u4e8c\u8005\u7684\u5173\u7cfb\u67e5\u8fd9\u8868\u5c31\u53ef\u4ee5\u4e86\ncreate table author2book(\nid int not null unique auto_increment,\nauthor_id int not null,\nbook_id int not null,\nconstraint fk_author foreign key(author_id) references author(id)\non delete cascade\non update cascade,\nconstraint fk_book foreign key(book_id) references book(id)\non delete cascade\non update cascade,\nprimary key(author_id,book_id)\n);\n\n#\u63d2\u5165\u56db\u4e2a\u4f5c\u8005\uff0cid\u4f9d\u6b21\u6392\u5f00\ninsert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');\n\n#\u6bcf\u4e2a\u4f5c\u8005\u4e0e\u81ea\u5df1\u7684\u4ee3\u8868\u4f5c\u5982\u4e0b\n1 egon: \n 1 \u4e5d\u9633\u795e\u529f\n 2 \u4e5d\u9634\u771f\u7ecf\n 3 \u4e5d\u9634\u767d\u9aa8\u722a\n 4 \u72ec\u5b64\u4e5d\u5251\n 5 \u964d\u9f99\u5341\u5df4\u638c\n 6 \u8475\u82b1\u5b9d\u5178\n\n2 alex: \n 1 \u4e5d\u9633\u795e\u529f\n 6 \u8475\u82b1\u5b9d\u5178\n\n3 yuanhao:\n 4 \u72ec\u5b64\u4e5d\u5251\n 5 \u964d\u9f99\u5341\u5df4\u638c\n 6 \u8475\u82b1\u5b9d\u5178\n\n4 wpq:\n 1 \u4e5d\u9633\u795e\u529f\n\ninsert into author2book(author_id,book_id) values\n(1,1),\n(1,2),\n(1,3),\n(1,4),\n(1,5),\n(1,6),\n(2,1),\n(2,6),\n(3,4),\n(3,5),\n(3,6),\n(4,1)\n;\n\u5176\u4ed6\u4f8b\u5b50\n\u5355\u5f20\u8868\uff1a\u7528\u6237\u8868+\u76f8\u4eb2\u5173\u7cfb\u8868\uff0c\u76f8\u5f53\u4e8e\uff1a\u7528\u6237\u8868+\u76f8\u4eb2\u5173\u7cfb\u8868+\u7528\u6237\u8868\n\u591a\u5f20\u8868\uff1a\u7528\u6237\u8868+\u7528\u6237\u4e0e\u4e3b\u673a\u5173\u7cfb\u8868+\u4e3b\u673a\u8868\n\n\u4e2d\u95f4\u90a3\u4e00\u5f20\u5b58\u653e\u5173\u7cfb\u7684\u8868\uff0c\u5bf9\u5916\u5173\u8054\u7684\u5b57\u6bb5\u53ef\u4ee5\u8054\u5408\u552f\u4e00\n#\u4e00\u5bf9\u4e00\n\u4e24\u5f20\u8868\uff1a\u5b66\u751f\u8868\u548c\u5ba2\u6237\u8868\n\n\u4e00\u5bf9\u4e00\uff1a\u4e00\u4e2a\u5b66\u751f\u662f\u4e00\u4e2a\u5ba2\u6237\uff0c\u4e00\u4e2a\u5ba2\u6237\u6709\u53ef\u80fd\u53d8\u6210\u4e00\u4e2a\u5b66\u6821\uff0c\u5373\u4e00\u5bf9\u4e00\u7684\u5173\u7cfb\n\n\u5173\u8054\u65b9\u5f0f\uff1aforeign key+unique\n#\u4e00\u5b9a\u662fstudent\u6765foreign key\u8868customer\uff0c\u8fd9\u6837\u5c31\u4fdd\u8bc1\u4e86\uff1a\n#1 \u5b66\u751f\u4e00\u5b9a\u662f\u4e00\u4e2a\u5ba2\u6237\uff0c\n#2 \u5ba2\u6237\u4e0d\u4e00\u5b9a\u662f\u5b66\u751f\uff0c\u4f46\u6709\u53ef\u80fd\u6210\u4e3a\u4e00\u4e2a\u5b66\u751f\n\ncreate table customer(\nid int primary key auto_increment,\nname varchar(20) not null,\nqq varchar(10) not null,\nphone char(16) not null\n);\n\ncreate table student(\nid int primary key auto_increment,\nclass_name varchar(20) not null,\ncustomer_id int unique, #\u8be5\u5b57\u6bb5\u4e00\u5b9a\u8981\u662f\u552f\u4e00\u7684\nforeign key(customer_id) references customer(id) #\u5916\u952e\u7684\u5b57\u6bb5\u4e00\u5b9a\u8981\u4fdd\u8bc1unique\non delete cascade\non update cascade\n);\n\n#\u589e\u52a0\u5ba2\u6237\ninsert into customer(name,qq,phone) values\n('\u674e\u98de\u673a','31811231',13811341220),\n('\u738b\u5927\u70ae','123123123',15213146809),\n('\u5b88\u69b4\u5f39','283818181',1867141331),\n('\u5434\u5766\u514b','283818181',1851143312),\n('\u8d62\u706b\u7bad','888818181',1861243314),\n('\u6218\u5730\u96f7','112312312',18811431230)\n;\n\n#\u589e\u52a0\u5b66\u751f\ninsert into student(class_name,customer_id) values\n('\u8131\u4ea73\u73ed',3),\n('\u5468\u672b19\u671f',4),\n('\u5468\u672b19\u671f',5)\n;\n\u5176\u4ed6\u4f8b\u5b50\n\u4f8b\u4e00\uff1a\u4e00\u4e2a\u7528\u6237\u53ea\u6709\u4e00\u4e2a\u535a\u5ba2\n\n \u7528\u6237\u8868\uff1a\n id name\n 1 egon\n 2 alex\n 3 wupeiqi\n\n \u535a\u5ba2\u8868 \n fk+unique\n id url name_id\n 1 xxxx 1\n 2 yyyy 3\n 3 zzz 2\n\n\u4f8b\u4e8c\uff1a\u4e00\u4e2a\u7ba1\u7406\u5458\u552f\u4e00\u5bf9\u5e94\u4e00\u4e2a\u7528\u6237\n \u7528\u6237\u8868\uff1a\n id user password\n 1 egon xxxx\n 2 alex yyyy\n\n \u7ba1\u7406\u5458\u8868\uff1a\n fk+unique\n id user_id password\n 1 1 xxxxx\n 2 2 yyyyy<\/code><\/pre>\n7\u3001\u4f5c\u4e1a<\/h3>\n
\u7ec3\u4e60\uff1a\u8d26\u53f7\u4fe1\u606f\u8868\uff0c\u7528\u6237\u7ec4\uff0c\u4e3b\u673a\u8868\uff0c\u4e3b\u673a\u7ec4<\/p>\n
\u7528\u6237\u8868<\/p>\n
create table user( id int not null unique auto_increment, username varchar(20) not null, password varchar(50) not null, primary key(username,password) );\ninsert into user(username,password) values (\u2018root\u2019,\u2018123\u2019), (\u2018egon\u2019,\u2018456\u2019), (\u2018alex\u2019,\u2018alex3714\u2019) ;<\/code><\/pre>\n\u7528\u6237\u7ec4\u8868<\/p>\n
create table usergroup( id int primary key auto_increment, groupname varchar(20) not null unique );\ninsert into usergroup(groupname) values (\u2018IT\u2019), (\u2018Sale\u2019), (\u2018Finance\u2019), (\u2018boss\u2019) ;<\/code><\/pre>\n\u4e3b\u673a\u8868<\/p>\n
create table host( id int primary key auto_increment, ip char(15) not null unique default \u2018127.0.0.1\u2019 );\ninsert into host(ip) values (\u2018172.16.45.2\u2019), (\u2018172.16.31.10\u2019), (\u2018172.16.45.3\u2019), (\u2018172.16.31.11\u2019), (\u2018172.10.45.3\u2019), (\u2018172.10.45.4\u2019), (\u2018172.10.45.5\u2019), (\u2018192.168.1.20\u2019), (\u2018192.168.1.21\u2019), (\u2018192.168.1.22\u2019), (\u2018192.168.2.23\u2019), (\u2018192.168.2.223\u2019), (\u2018192.168.2.24\u2019), (\u2018192.168.3.22\u2019), (\u2018192.168.3.23\u2019), (\u2018192.168.3.24\u2019) ;<\/code><\/pre>\n\u4e1a\u52a1\u7ebf\u8868<\/p>\n
create table business( id int primary key auto_increment, business varchar(20) not null unique ); insert into business(business) values (\u2018\u8f7b\u677e\u8d37\u2019), (\u2018\u968f\u4fbf\u82b1\u2019), (\u2018\u5927\u5bcc\u7fc1\u2019), (\u2018\u7a77\u4e00\u751f\u2019) ;<\/code><\/pre>\n\u5efa\u5173\u7cfb\uff1auser\u4e0eusergroup<\/p>\n
create table user2usergroup( id int not null unique auto_increment, user_id int not null, group_id int not null, primary key(user_id,group_id), foreign key(user_id) references user(id), foreign key(group_id) references usergroup(id) );\ninsert into user2usergroup(user_id,group_id) values (1,1), (1,2), (1,3), (1,4), (2,3), (2,4), (3,4) ;<\/code><\/pre>\n\u5efa\u5173\u7cfb\uff1ahost\u4e0ebusiness<\/p>\n
create table host2business( id int not null unique auto_increment, host_id int not null, business_id int not null, primary key(host_id,business_id), foreign key(host_id) references host(id), foreign key(business_id) references business(id) );\ninsert into host2business(host_id,business_id) values (1,1), (1,2), (1,3), (2,2), (2,3), (3,4) ;<\/code><\/pre>\n\u5efa\u5173\u7cfb\uff1auser\u4e0ehost<\/p>\n
create table user2host( id int not null unique auto_increment, user_id int not null, host_id int not null, primary key(user_id,host_id), foreign key(user_id) references user(id), foreign key(host_id) references host(id) );\ninsert into user2host(user_id,host_id) values (1,1), (1,2), (1,3), (1,4), (1,5), (1,6), (1,7), (1,8), (1,9), (1,10), (1,11), (1,12), (1,13), (1,14), (1,15), (1,16), (2,2), (2,3), (2,4), (2,5), (3,10), (3,11), (3,12) ; ```<\/code><\/pre>\n\u4f5c\u4e1a\uff1a<\/p>\n
<\/div><\/p>\n
\u56fe12-2 \u6570\u636e\u5e93\u8868\u7ed3\u6784<\/p>\n
\u4e03\u3001\u4fee\u6539\u8868ALTER TABLE<\/h2>\n1. \u4fee\u6539\u8868\u540d\n ALTER TABLE \u8868\u540d \n RENAME \u65b0\u8868\u540d;\n\n2. \u589e\u52a0\u5b57\u6bb5\n ALTER TABLE \u8868\u540d\n ADD \u5b57\u6bb5\u540d \u6570\u636e\u7c7b\u578b [\u5b8c\u6574\u6027\u7ea6\u675f\u6761\u4ef6\u2026],\n ADD \u5b57\u6bb5\u540d \u6570\u636e\u7c7b\u578b [\u5b8c\u6574\u6027\u7ea6\u675f\u6761\u4ef6\u2026];\n ALTER TABLE \u8868\u540d\n ADD \u5b57\u6bb5\u540d \u6570\u636e\u7c7b\u578b [\u5b8c\u6574\u6027\u7ea6\u675f\u6761\u4ef6\u2026] FIRST;\n ALTER TABLE \u8868\u540d\n ADD \u5b57\u6bb5\u540d \u6570\u636e\u7c7b\u578b [\u5b8c\u6574\u6027\u7ea6\u675f\u6761\u4ef6\u2026] AFTER \u5b57\u6bb5\u540d;\n\n3. \u5220\u9664\u5b57\u6bb5\n ALTER TABLE \u8868\u540d \n DROP \u5b57\u6bb5\u540d;\n\n4. \u4fee\u6539\u5b57\u6bb5\n ALTER TABLE \u8868\u540d \n MODIFY \u5b57\u6bb5\u540d \u6570\u636e\u7c7b\u578b [\u5b8c\u6574\u6027\u7ea6\u675f\u6761\u4ef6\u2026];\n ALTER TABLE \u8868\u540d \n CHANGE \u65e7\u5b57\u6bb5\u540d \u65b0\u5b57\u6bb5\u540d \u65e7\u6570\u636e\u7c7b\u578b [\u5b8c\u6574\u6027\u7ea6\u675f\u6761\u4ef6\u2026];\n ALTER TABLE \u8868\u540d \n CHANGE \u65e7\u5b57\u6bb5\u540d \u65b0\u5b57\u6bb5\u540d \u65b0\u6570\u636e\u7c7b\u578b [\u5b8c\u6574\u6027\u7ea6\u675f\u6761\u4ef6\u2026];\n\u793a\u4f8b\uff1a\n1. \u4fee\u6539\u5b58\u50a8\u5f15\u64ce\nmysql> alter table service \n -> engine=innodb;\n\n2. \u6dfb\u52a0\u5b57\u6bb5\nmysql> alter table student10\n -> add name varchar(20) not null,\n -> add age int(3) not null default 22;\n\nmysql> alter table student10\n -> add stu_num varchar(10) not null after name; \/\/\u6dfb\u52a0name\u5b57\u6bb5\u4e4b\u540e\n\nmysql> alter table student10 \n -> add sex enum('male','female') default 'male' first; \/\/\u6dfb\u52a0\u5230\u6700\u524d\u9762\n\n3. \u5220\u9664\u5b57\u6bb5\nmysql> alter table student10\n -> drop sex;\n\nmysql> alter table service\n -> drop mac;\n\n4. \u4fee\u6539\u5b57\u6bb5\u7c7b\u578bmodify\nmysql> alter table student10\n -> modify age int(3);\nmysql> alter table student10\n -> modify id int(11) not null primary key auto_increment; \/\/\u4fee\u6539\u4e3a\u4e3b\u952e\n\n5. \u589e\u52a0\u7ea6\u675f\uff08\u9488\u5bf9\u5df2\u6709\u7684\u4e3b\u952e\u589e\u52a0auto_increment\uff09\nmysql> alter table student10 modify id int(11) not null primary key auto_increment;\nERROR 1068 (42000): Multiple primary key defined\n\nmysql> alter table student10 modify id int(11) not null auto_increment;\nQuery OK, 0 rows affected (0.01 sec)\nRecords: 0 Duplicates: 0 Warnings: 0\n\n6. \u5bf9\u5df2\u7ecf\u5b58\u5728\u7684\u8868\u589e\u52a0\u590d\u5408\u4e3b\u952e\nmysql> alter table service2\n -> add primary key(host_ip,port); \n\n7. \u589e\u52a0\u4e3b\u952e\nmysql> alter table student1\n -> modify name varchar(10) not null primary key;\n\n8. \u589e\u52a0\u4e3b\u952e\u548c\u81ea\u52a8\u589e\u957f\nmysql> alter table student1\n -> modify id int not null primary key auto_increment;\n\n9. \u5220\u9664\u4e3b\u952e\na. \u5220\u9664\u81ea\u589e\u7ea6\u675f\nmysql> alter table student10 modify id int(11) not null; \n\nb. \u5220\u9664\u4e3b\u952e\nmysql> alter table student10 \n -> drop primary key;<\/code><\/pre>\n\u516b\u3001\u590d\u5236\u8868<\/h2>\n\u590d\u5236\u8868\u7ed3\u6784\uff0b\u8bb0\u5f55 \uff08key\u4e0d\u4f1a\u590d\u5236: \u4e3b\u952e\u3001\u5916\u952e\u548c\u7d22\u5f15\uff09\nmysql> create table new_service select * from service;\n\n\u53ea\u590d\u5236\u8868\u7ed3\u6784\nmysql> select * from service where 1=2; \/\/\u6761\u4ef6\u4e3a\u5047\uff0c\u67e5\u4e0d\u5230\u4efb\u4f55\u8bb0\u5f55\nEmpty set (0.00 sec)\nmysql> create table new1_service select * from service where 1=2; \nQuery OK, 0 rows affected (0.00 sec)\nRecords: 0 Duplicates: 0 Warnings: 0\n\nmysql> create table t4 like employees;<\/code><\/pre>\n\u4e5d\u3001\u5220\u9664\u8868<\/h2>\n
DROP<\/strong> TABLE<\/strong> \u8868\u540d;<\/p>\n","protected":false},"excerpt":{"rendered":"\u8868\u76f8\u5173\u64cd\u4f5c \u4e00\u3001\u5b58\u50a8\u5f15\u64ce\u4ecb\u7ecd \u5b58\u50a8\u5f15\u64ce\u5373\u8868\u7c7b\u578b\uff0cmysql\u6839\u636e\u4e0d\u540c\u7684\u8868\u7c7b\u578b\u4f1a\u6709\u4e0d\u540c\u7684\u5904\u7406\u673a\u5236 1\u3001\u4ec0\u4e48\u662f\u5b58\u50a8\u5f15 […]<\/p>\n","protected":false},"author":3,"featured_media":6744,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[3,403],"tags":[],"_links":{"self":[{"href":"https:\/\/egonlin.com\/index.php?rest_route=\/wp\/v2\/posts\/4895"}],"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=4895"}],"version-history":[{"count":0,"href":"https:\/\/egonlin.com\/index.php?rest_route=\/wp\/v2\/posts\/4895\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/egonlin.com\/index.php?rest_route=\/wp\/v2\/media\/6744"}],"wp:attachment":[{"href":"https:\/\/egonlin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4895"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/egonlin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4895"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/egonlin.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4895"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"https:\/\/egonlin.com\/wp-content\/uploads\/2022\/03\/\u8868\u76f8\u5173\u64cd\u4f5c1.jpg\")
<\/div><\/p>\n![\"\"](\"https:\/\/egonlin.com\/wp-content\/uploads\/2022\/03\/wps5.jpg\")
<\/div><\/p>\n![\"file\"](\"https:\/\/egonlin.com\/wp-content\/uploads\/2022\/03\/image-1703757448588.png\")
<\/div>![\"file\"](\"https:\/\/egonlin.com\/wp-content\/uploads\/2022\/03\/image-1703757740566.png\")
<\/div>![\"\"](\"https:\/\/egonlin.com\/wp-content\/uploads\/2022\/03\/wps6.png\")
<\/div><\/p>\n