mysql 系统表_mysql一些重要的系统表

SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS;

tables 提供表信息.

SELECT table_name, table_type, engine

FROM information_schema.tables

WHERE table_schema = 'test'

ORDER BY table_name;

COLUMNS 提供表的字段信息.

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT

FROM INFORMATION_SCHEMA.COLUMNS

WHERE table_name = 'a'

AND table_schema = 'test';

STATISTICS 提供表的索引信息.

SELECT * FROM INFORMATION_SCHEMA.STATISTICS

WHERE table_name = 'b'

AND table_schema = 'test'

TRIGGERS 提供表的触发器信息.

drop TRIGGER if exists test_trigger;

CREATE TRIGGER test_trigger AFTER INSERT ON a FOR EACH ROW

BEGIN

SET @id = new.id;

SET @NAME = new. NAME;

SET @pwd = new.pwd;

UPDATE pivot

SET Author = (

SELECT

IF (

@attribute = 'Author' ,@VALUE,

Author

)

),

Title = (

SELECT

IF (

@attribute = 'Title' ,@VALUE,

Title

)

),

Publisher = (

SELECT

IF (

@attribute = 'Publisher' ,@VALUE,

Publisher

)

)

WHERE

id =@id;

END

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS

WHERE TRIGGER_SCHEMA='test' AND TRIGGER_NAME='test_trigger';

VIEWS 提供表的视图信息.

CREATE VIEW test.v AS SELECT 'a' || 'b' as col1;

SELECT * FROM INFORMATION_SCHEMA.VIEWS

WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';