Chapter聽23.聽The INFORMATION_SCHEMA Information Database - MySQL 5.1参考手册中文版

璇ユ柟妗堟槸鏁版嵁搴擄紝鍥犳��SCHEMATA琛ㄦ彁渚涗簡鍏充簬鏁版嵁搴撶殑淇℃伅銆�

娉ㄩ噴锛�SQL_PATH鍒楃殑涔嬫�讳负NULL銆�

涓嬭堪璇�鍙ユ槸绛夋晥鐨勶��

SELECT SCHEMA_NAME AS `Database

FROM INFORMATION_SCHEMA.SCHEMATA

[WHERE SCHEMA_NAME LIKE 'wild']

SHOW DATABASES

[LIKE 'wild']

TABLES琛ㄧ粰鍑轰簡鍏充簬鏁版嵁搴撲腑鐨勮〃鐨勪俊鎭�銆�

娉ㄩ噴锛�

路         TABLE_SCHEMA鍜�TABLE_NAME鏄�SHOW鏄剧ず涓�鐨勫崟涓�瀛楁�碉紝渚嬪�俆able_in_db1銆�

路         TABLE_TYPE锛堣〃绫诲瀷锛夊簲鏄�BASE TABLE锛堝熀鏈�琛�锛�鎴�VIEW锛堣�嗗浘锛夈�傚�傛灉琛ㄦ槸涓存椂鎬х殑锛�TABLE_TYPE = TEMPORARY銆傦紙娌℃湁涓存椂瑙嗗浘锛屽洜姝わ紝鍥犳�や笉瀛樺湪姝т箟锛夈��

路         濡傛灉琛ㄤ綅浜�INFORMATION_SCHEMA鏁版嵁搴撲腑锛孴ABLE_ROWS鍒椾负NULL銆傚�逛��InnoDB琛�锛屽湪SQL浼樺寲涓�锛岃�岃�℃暟浠呮槸澶ф�備及璁″�笺��

路         娌℃湁鍏充簬琛ㄩ粯璁ゅ瓧绗﹂泦鐨勪换浣曚俊鎭�銆�TABLE_COLLATION澶勪簬鍏抽棴鐘舵�侊紝鍘熷洜鍦ㄤ簬鏍″�瑰悕绉颁互瀛楃�﹂泦鍚嶇О寮�澶淬��

涓嬭堪璇�鍙ユ槸绛夋晥鐨勶��

SELECT table_name FROM INFORMATION_SCHEMA.TABLES

  [WHERE table_schema = 'db_name']

  [WHERE|AND table_name LIKE 'wild']

SHOW TABLES

  [FROM db_name]

  [LIKE 'wild']

COLUMNS琛ㄧ粰鍑轰簡琛ㄤ腑鐨勫垪淇℃伅銆�

娉ㄩ噴锛�

路         鍦�SHOW涓�锛岀被鍨嬫樉绀哄寘鎷�鏉ヨ嚜鏁颁釜涓嶅悓COLUMNS鍒楃殑鍊笺��

路         ORDINAL_POSITION鏈夊繀瑕侊紝杩欐槸鍥犱负锛屼綘鍙�鑳戒細鍦ㄦ煇涓�澶╅渶瑕�ORDER BY ORDINAL_POSITION锛堟寜ORDINAL_POSITION鎺掑簭锛�銆備笉鍚屼簬SHOW锛孲ELECT娌℃湁鑷�鍔ㄦ帓搴忓姛鑳姐��

路         CHARACTER_OCTET_LENGTH搴斾笌CHARACTER_MAXIMUM_LENGTH鐩稿悓锛屼絾澶氬瓧鑺傚瓧绗﹂泦闄ゅ�栥��

路         CHARACTER_SET_NAME鍙�鐢�Collation锛堟牎瀵癸級瀵煎嚭銆備緥濡傦紝濡傛灉缁欏嚭浜嗏��SHOW FULL COLUMNS FROM t鈥濓紝鍦�Collation锛堟牎瀵癸級鍒椾腑灏嗚�佸��latin1_swedish_ci鐨勫�硷紝瀛楃�﹂泦鐢辩��1涓�涓嬪垝绾垮墠鐨勫悕绉版寚鏄庛�俵atin1.

涓嬭堪璇�鍙ユ槸绛夋晥鐨勶��

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT

  FROM INFORMATION_SCHEMA.COLUMNS

  WHERE table_name = 'tbl_name'

  [AND table_schema = 'db_name']

  [AND column_name LIKE 'wild']

SHOW COLUMNS

  FROM tbl_name

  [FROM db_name]

  [LIKE wild]

STATISTICS琛ㄧ粰鍑轰簡鍏充簬琛ㄧ储寮曠殑淇℃伅銆�

娉ㄩ噴锛�

路         娌℃湁鍏充簬杩欎簺绱㈠紩鐨勬爣鍑嗚〃銆備笂闈㈢殑鍒楄〃涓嶴QL鏈嶅姟鍣�2000涓�sp_statistics杩斿洖鐨勫�肩被浼笺�備笉鍚屼箣澶勫湪浜庣敤CATALOG鏇挎崲浜哘UALIFIER锛屽苟鐢⊿CHEMA鏇挎崲浜哋WNER銆�

鏄捐�屾槗瑙侊紝鍓嶈堪琛ㄥ拰SHOW INDEX鐨勮緭鍑哄潎鏄�鐢辩浉鍚岀殑鐖跺�硅薄瀵煎嚭鐨勩��鍥犳�わ紝鐩稿叧鎬у凡鍏抽棴銆�

涓嬭堪璇�鍙ユ槸绛夋晥鐨勶��

SELECT * FROM INFORMATION_SCHEMA.STATISTICS

  WHERE table_name = 'tbl_name'

  [AND table_schema = 'db_name']

SHOW INDEX

  FROM tbl_name

  [FROM db_name]

USER_PRIVILEGES锛堢敤鎴锋潈闄愶級琛ㄧ粰鍑轰簡鍏充簬鍏ㄧ▼鏉冮檺鐨勪俊鎭�銆�璇ヤ俊鎭�婧愯��mysql.user鎺堟潈琛ㄣ��

娉ㄩ噴锛�

路         杩欐槸涓�涓�闈炴爣鍑嗚〃銆傚叾鍊兼潵鑷�mysql.user琛ㄣ��

SCHEMA_PRIVILEGES锛堟柟妗堟潈闄愶級琛ㄧ粰鍑轰簡鍏充簬鏂规�堬紙鏁版嵁搴擄級鏉冮檺鐨勪俊鎭�銆傝�ヤ俊鎭�鏉ヨ嚜mysql.db鎺堟潈琛ㄣ��

娉ㄩ噴锛�

路         杩欐槸涓�涓�闈炴爣鍑嗚〃銆傚叾鍊兼潵鑷�mysql.db琛ㄣ��

TABLE_PRIVILEGES锛堣〃鏉冮檺锛�琛ㄧ粰鍑轰簡鍏充簬琛ㄦ潈闄愮殑淇℃伅銆傝�ヤ俊鎭�婧愯嚜mysql.tables_priv鎺堟潈琛ㄣ��

涓嬭堪璇�鍙ヤ笉绛夋晥锛�

SELECT ... FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES

SHOW GRANTS ...

PRIVILEGE_TYPE鍙�浠ュ寘鍚�杩欎簺鍊间箣涓�锛堜粎鑳戒竴涓�锛夛��SELECT銆�INSERT銆�UPDATE銆�REFERENCES銆�ALTER銆�INDEX銆�DROP銆丆REATE VIEW銆�

COLUMN_PRIVILEGES锛堝垪鏉冮檺锛夎〃缁欏嚭浜嗗叧浜庡垪鏉冮檺鐨勪俊鎭�銆傝�ヤ俊鎭�婧愯嚜mysql.columns_priv鎺堟潈琛ㄣ��

娉ㄩ噴锛�

路         鍦�SHOW FULL COLUMNS锛堟樉绀哄畬鏁村垪锛夌殑杈撳嚭涓�锛屾潈闄愬�间綅浜庝竴涓�瀛楁�靛苟閲囩敤灏忓啓褰㈠紡锛屼緥濡俿elect銆乮nsert銆乽pdate銆乺eferences銆�鍦�COLUMN_PRIVILEGES涓�锛屾瘡绉嶆潈闄愬崰涓�琛岋紝骞朵负澶у啓褰㈠紡銆�

路         PRIVILEGE_TYPE鍙�浠ュ寘鍚�杩欎簺鍊间箣涓�锛堜粎鑳戒竴涓�锛夛��SELECT, INSERT, UPDATE, REFERENCES.

路         濡傛灉鐢ㄦ埛鏈�GRANT OPTION鏉冮檺锛�閭ｄ箞IS_GRANTABLE搴斾负YES銆傚惁鍒欙紝IS_GRANTABLE搴斾负NO銆傚湪杈撳嚭涓�锛屼笉浼氬皢GRANT OPTION浣滀负鍗曠嫭鏉冮檺鍒楀嚭銆�

涓嬭堪璇�鍙ヤ笉绛夋晥锛�

SELECT ... FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES

SHOW GRANTS ...

CHARACTER_SETS锛堝瓧绗﹂泦锛夎〃鎻愪緵浜嗗叧浜庡彲鐢ㄥ瓧绗﹂泦鐨勪俊鎭�銆�

娉ㄩ噴锛�

路         鎴戜滑澧炲姞浜嗕袱涓�闈炴爣鍑嗗垪锛屽垎鍒�瀵瑰簲浜�SHOW CHARACTER SET杈撳嚭鐨凞escription锛堟弿杩帮級鍜孧axlen锛堟渶澶ч暱搴︼級鍒�銆�

涓嬭堪璇�鍙ユ槸绛夋晥鐨勶��

SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS

  [WHERE name LIKE 'wild']

SHOW CHARACTER SET

  [LIKE 'wild']

COLLATIONS琛ㄦ彁渚涗簡鍏充簬鍚勫瓧绗﹂泦鐨勫�圭収淇℃伅銆�

娉ㄩ噴锛�

路         鎴戜滑澧炲姞浜�5涓�闈炴爣鍑嗗垪锛屽垎鍒�瀵瑰簲浜�SHOW COLLATION杈撳嚭鐨凜harset銆�Id銆�Default銆�Compiled鍜�Sortlen鍒�銆�

涓嬭堪璇�鍙ユ槸绛夋晥鐨勶��

SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS

  [WHERE collation_name LIKE 'wild']

SHOW COLLATION

  [LIKE 'wild']

COLLATION_CHARACTER_SET_APPLICABILITY琛ㄦ寚鏄庝簡鍙�鐢ㄤ簬鏍″�圭殑瀛楃�﹂泦銆傝繖浜涘垪绛夋晥浜�SHOW COLLATION鐨勫墠涓や釜鏄剧ず瀛楁�点��

TABLE_CONSTRAINTS琛ㄦ弿杩颁簡瀛樺湪绾︽潫鐨勮〃銆�

娉ㄩ噴锛�

路         CONSTRAINT_TYPE鐨勫�煎彲浠ユ槸UNIQUE锛堝敮涓�锛�銆�PRIMARY KEY锛堜富閿�锛�鎴�FOREIGN KEY锛堝�栭敭锛夈��

路         褰�Non_unique瀛楁�典��0鏃讹紝UNIQUE鍜�PRIMARY KEY淇℃伅涓嶴HOW INDEX杈撳嚭鐨凨ey_name瀛楁�典腑缁欏嚭鐨勪俊鎭�鍩烘湰鐩稿悓銆�

路         CONSTRAINT_TYPE鍒楀彲鍖呭惈涓嬭堪鍊间箣涓�锛�UNIQUE銆�PRIMARY KEY銆�FOREIGN KEY銆�CHECK銆傝繖鏄�涓�涓狢HAR锛堥潪ENUM锛夊垪銆�鍦ㄦ垜浠�鏀�鎸丆HECK鍓嶏紝CHECK鍊间笉鍙�鐢ㄣ��

KEY_COLUMN_USAGE琛ㄦ弿杩颁簡鍏锋湁绾︽潫鐨勯敭鍒椼��

娉ㄩ噴锛�

路         濡傛灉绾︽潫涓哄�栭敭锛岃繖灏辨槸澶栭敭鍒楋紝鑰屼笉鏄�澶栭敭寮曠敤鐨勫垪銆�

路         ORDINAL_POSITION鐨勫�兼槸鍒楀湪绾︽潫涓�鐨勪綅缃�锛岃�屼笉鏄�鍒楀湪琛ㄤ腑鐨勪綅缃�銆�鍒椾綅缃�閲囩敤浠�1寮�濮嬬殑鏁板�肩紪鍙枫��

路         瀵逛簬鈥滃敮涓�鈥濆拰鈥滀富閿�鈥濈害鏉燂紝POSITION_IN_UNIQUE_CONSTRAINT鐨勫�间负NULL銆�瀵逛簬鈥滃�栭敭鈥濈害鏉燂紝瀹冩槸鎵�寮曠敤琛ㄥ唴閿�涓�鐨勯『搴忎綅缃�銆�

渚嬪�傦紝鍋囧畾鏈変袱涓�鍏锋湁涓嬭堪瀹氫箟鐨勮〃t1鍜宼3锛�

CREATE TABLE t1

(

s1 INT,

s2 INT,

s3 INT,

PRIMARY KEY(s3)

) ENGINE=InnoDB;

CREATE TABLE t3

(

s1 INT,

s2 INT,

s3 INT,

KEY(s1),

CONSTRAINT CO FOREIGN KEY (s2) REFERENCES t1(s3)

) ENGINE=InnoDB;

瀵逛簬杩欎袱涓�琛�锛�KEY_COLUMN_USAGE琛ㄦ湁涓よ�岋��

o        涓�琛屽惈鏈�CONSTRAINT_NAME='PRIMARY', TABLE_NAME='t1', COLUMN_NAME='s3', ORDINAL_POSITION=1, POSITION_IN_UNIQUE_CONSTRAINT=NULL銆�

o        鍙︿竴琛屽惈鏈�CONSTRAINT_NAME='CO', TABLE_NAME='t3', COLUMN_NAME='s2', ORDINAL_POSITION=1, POSITION_IN_UNIQUE_CONSTRAINT=1銆�

ROUTINES琛ㄦ彁渚涗簡鍏充簬瀛樺偍瀛愮▼搴忥紙瀛樺偍绋嬪簭鍜屽嚱鏁帮級鐨勪俊鎭�銆�姝ゆ椂锛�ROUTINES琛ㄤ笉鍖呭惈鑷�瀹氫��鍑芥暟锛圲DF锛夈��

鍚嶄负鈥�mysql.proc name鈥濈殑鍒楁寚鏄庝簡瀵瑰簲浜�INFORMATION_SCHEMA.ROUTINES琛ㄧ殑mysql.proc琛ㄥ垪锛屽�傛灉鏈夌殑璇濄��

娉ㄩ噴锛�

路         MySQL璁＄畻EXTERNAL_LANGUAGE锛屽洜姝わ細

o        濡傛灉mysql.proc.language='SQL'锛岄偅涔�EXTERNAL_LANGUAGE涓�NULL銆�

o        鍚﹀垯锛�EXTERNAL_LANGUAGE涓�mysql.proc.language涓�鐨勫��銆傜劧鑰岋紝鐢变簬灏氭病鏈夊�栭儴璇�瑷�锛屽洜姝よ�ュ�兼�讳负NULL銆�

VIEWS琛ㄧ粰鍑轰簡鍏充簬鏁版嵁搴撲腑鐨勮�嗗浘鐨勪俊鎭�銆�

娉ㄩ噴锛�

路         鏈変竴绉嶆柊鐨勬潈闄�SHOW VIEW锛屽�傛灉娌℃湁瀹冿紝灏嗘棤娉曠湅鍒癡IEWS琛ㄣ��

路         VIEW_DEFINITION鍒楀惈鏈変綘鍦⊿HOW CREATE VIEW鎵�鐢熸垚鐨凜reate Table瀛楁�典腑瑙佸埌鐨勫ぇ澶氭暟淇℃伅銆�璺宠繃SELECT鍓嶇殑鍗曡瘝锛屽苟璺宠繃鍏锋湁CHECK OPTION锛堟��鏌ラ�夐」锛夌殑鍗曡瘝銆備緥濡傦紝濡傛灉鍒濆�嬭��鍙ユ槸锛�

路                CREATE VIEW v AS

路                  SELECT s2,s1 FROM t

路                  WHERE s1 > 5

路                  ORDER BY s1

路                  WITH CHECK OPTION;

閭ｄ箞瑙嗗浘瀹氫箟涓猴細

SELECT s2,s1 FROM t WHERE s1 > 5 ORDER BY s1

路         CHECK_OPTION鍒楃殑鍊兼�讳负NONE銆�

路         濡傛灉瑙嗗浘鏄�鍙�鏇存柊鐨勶紝IS_UPDATABLE鍒楃殑鍊间负YES锛屽�傛��瑙嗗浘鏄�涓嶅彲鏇存柊鐨勶��IS_UPDATABLE鍒楃殑鍊间负NO銆�

路         DEFINER鍒楁寚鏄庝簡瀹氫箟瑙嗗浘鐨勪汉銆�SECURITY_TYPE鐨勫�间负DEFINER鎴�INVOKER銆�

TRIGGERS琛ㄦ彁渚涗簡鍏充簬瑙﹀彂绋嬪簭鐨勪俊鎭�銆�

蹇呴』鏈�SUPER鏉冮檺鎵嶈兘鏌ョ湅璇ヨ〃銆�

娉ㄩ噴锛�

路         TRIGGER_SCHEMA鍜�TRIGGER_NAME鍒椾腑鍒嗗埆鍚�鏈夌浉搴旀暟鎹�搴撶殑鍚嶇О浠ュ強瑙﹀彂绋嬪簭鐨勫悕绉帮紝鍦ㄨ�ユ暟鎹�搴撲腑锛屽惈鏈夎�ヨЕ鍙戠▼搴忋��

路         EVENT_MANIPULATION鍒楀惈鏈変笅杩板�间箣涓�锛�INSERT銆丏ELETE銆佹垨UPDATE銆�

路         姝ｅ��绗�21绔狅細瑙﹀彂绋嬪簭涓�鎸囧嚭鐨勯偅鏍凤紝姣忎釜瑙﹀彂绋嬪簭鍧囦笌涓�涓�琛ㄥ噯纭�鐩稿叧銆�EVENT_OBJECT_SCHEMA鍜�EVENT_OBJECT_TABLE鍒楀寘鍚�鐩稿簲鐨勬暟鎹�搴撳拰琛ㄥ悕锛屽湪璇ユ暟鎹�搴撲腑锛屽惈鏈夎�ヨ〃銆�

路         ACTION_ORDER璇�鍙ュ惈鏈夎Е鍙戠▼搴忓姩浣滐紙鍦ㄧ浉鍚岃〃涓婃墍鏈夌被浼艰Е鍙戠▼搴忓垪琛ㄤ腑锛夌殑椤哄簭浣嶇疆銆傜洰鍓嶈�ュ�兼�讳负0锛岃繖鏄�鍥犱负鍦ㄧ浉鍚岃〃涓婂叿鏈夌浉鍚�EVENT_MANIPULATION鍜�ACTION_TIMING鐨勮Е鍙戠▼搴忎笉鑳借秴杩�1涓�銆�

路         ACTION_STATEMENT鍒楀惈鏈夋縺娲讳簡瑙﹀彂绋嬪簭鏃跺皢瑕佹墽琛岀殑璇�鍙ャ��杩欎笌SHOW TRIGGERS杈撳嚭鐨凷tatement锛堣��鍙ワ級鍒椾腑鏄剧ず鐨勬枃鏈�鐩稿悓銆�娉ㄦ剰锛岃�ユ枃鏈�閲囩敤浜哢TF-8缂栫爜鏂瑰紡銆�

路         ACTION_ORIENTATION鍒楁�诲惈鏈夊�尖��ROW鈥�銆�

路         ACTION_TIMING鍒楀惈鏈変笅杩颁袱绉嶅�间箣涓�锛�鈥�BEFORE鈥�鎴�鈥�AFTER鈥�銆�

路         鍒�ACTION_REFERENCE_OLD_ROW鍜�ACTION_REFERENCE_NEW_ROW鍒嗗埆鍚�鏈夋棫鐨勫拰鏂扮殑鍒楁爣璇嗙��銆傝繖鎰忓懗鐫�ACTION_REFERENCE_OLD_ROW鎬诲惈鏈夊�尖��OLD鈥�锛孉CTION_REFERENCE_NEW_ROW鎬诲惈鏈夊�尖��NEW鈥�銆�

路         SQL_MODE鍒楁樉绀轰簡鍒涘缓瑙﹀彂绋嬪簭鏃舵湁鏁堢殑鏈嶅姟鍣⊿QL妯″紡锛堟棤璁哄綋鍓嶇殑鏈嶅姟鍣⊿QL妯″紡涓轰綍锛屽彧瑕佹縺娲讳簡瑙﹀彂绋嬪簭锛屽畠灏嗕繚鎸佹湁鏁堬級銆傝�ュ垪鐨勫彲鑳藉彇鍊艰寖鍥翠笌sql_mode绯荤粺鍙橀噺鐨勫彇鍊艰寖鍥寸浉鍚屻�傝�峰弬瑙�5.3.2鑺傦紝鈥淪QL鏈嶅姟鍣ㄦā寮忊��銆�

路         鍦ㄤ笅杩板垪涓�锛岀洰鍓嶆�诲惈鏈塏ULL锛�TRIGGER_CATALOG锛�EVENT_OBJECT_CATALOG锛�ACTION_CONDITION锛�ACTION_REFERENCE_OLD_TABLE锛�ACTION_REFERENCE_NEW_TABLE鍜�CREATED銆�

渚嬪�傦紝浣跨��21.3鑺傦紝鈥滀娇鐢ㄨЕ鍙戠▼搴忊��涓�瀹氫箟鐨勮Е鍙戠▼搴�ins_sum銆�

mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS\G

*************************** 1. row ***************************

 TRIGGER_CATALOG: NULL

TRIGGER_SCHEMA: test

 TRIGGER_NAME: ins_sum

  EVENT_MANIPULATION: INSERT

EVENT_OBJECT_CATALOG: NULL

 EVENT_OBJECT_SCHEMA: test

  EVENT_OBJECT_TABLE: account

 ACTION_ORDER: 0

ACTION_CONDITION: NULL

ACTION_STATEMENT:  SET @sum = @sum + NEW.amount

  ACTION_ORIENTATION: ROW

 ACTION_TIMING: BEFORE

ACTION_REFERENCE_OLD_TABLE: NULL

ACTION_REFERENCE_NEW_TABLE: NULL

  ACTION_REFERENCE_OLD_ROW: OLD

  ACTION_REFERENCE_NEW_ROW: NEW

CREATED: NULL

1 row in set (1.54 sec)

鍙﹁�峰弬瑙�13.5.4.20鑺傦紝鈥淪HOW TRIGGERS璇�娉曗��銆�