is_statistics.result

来自「这个文件是windows mysql源码」· RESULT 代码 · 共 353 行 · 第 1/2 页

SHOW TABLES FROM information_schema LIKE 'STATISTICS';
Tables_in_information_schema (STATISTICS)
STATISTICS
#######################################################################
# Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
#######################################################################
DROP VIEW      IF EXISTS test.v1;
DROP PROCEDURE IF EXISTS test.p1;
DROP FUNCTION  IF EXISTS test.f1;
CREATE VIEW test.v1 AS     SELECT * FROM information_schema.STATISTICS;
CREATE PROCEDURE test.p1() SELECT * FROM information_schema.STATISTICS;
CREATE FUNCTION test.f1() returns BIGINT
BEGIN
DECLARE counter BIGINT DEFAULT NULL;
SELECT COUNT(*) INTO counter FROM information_schema.STATISTICS;
RETURN counter;
END//
# Attention: The printing of the next result sets is disabled.
SELECT * FROM information_schema.STATISTICS;
SELECT * FROM test.v1;
CALL test.p1;
SELECT test.f1();
DROP VIEW test.v1;
DROP PROCEDURE test.p1;
DROP FUNCTION test.f1;
#########################################################################
# Testcase 3.2.14.1: INFORMATION_SCHEMA.STATISTICS layout
#########################################################################
DESCRIBE          information_schema.STATISTICS;
Field	Type	Null	Key	Default	Extra
TABLE_CATALOG	varchar(512)	YES		NULL	
TABLE_SCHEMA	varchar(64)	NO			
TABLE_NAME	varchar(64)	NO			
NON_UNIQUE	bigint(1)	NO		0	
INDEX_SCHEMA	varchar(64)	NO			
INDEX_NAME	varchar(64)	NO			
SEQ_IN_INDEX	bigint(2)	NO		0	
COLUMN_NAME	varchar(64)	NO			
COLLATION	varchar(1)	YES		NULL	
CARDINALITY	bigint(21)	YES		NULL	
SUB_PART	bigint(3)	YES		NULL	
PACKED	varchar(10)	YES		NULL	
NULLABLE	varchar(3)	NO			
INDEX_TYPE	varchar(16)	NO			
COMMENT	varchar(16)	YES		NULL	
SHOW CREATE TABLE information_schema.STATISTICS;
Table	Create Table
STATISTICS	CREATE TEMPORARY TABLE `STATISTICS` (
  `TABLE_CATALOG` varchar(512) default NULL,
  `TABLE_SCHEMA` varchar(64) NOT NULL default '',
  `TABLE_NAME` varchar(64) NOT NULL default '',
  `NON_UNIQUE` bigint(1) NOT NULL default '0',
  `INDEX_SCHEMA` varchar(64) NOT NULL default '',
  `INDEX_NAME` varchar(64) NOT NULL default '',
  `SEQ_IN_INDEX` bigint(2) NOT NULL default '0',
  `COLUMN_NAME` varchar(64) NOT NULL default '',
  `COLLATION` varchar(1) default NULL,
  `CARDINALITY` bigint(21) default NULL,
  `SUB_PART` bigint(3) default NULL,
  `PACKED` varchar(10) default NULL,
  `NULLABLE` varchar(3) NOT NULL default '',
  `INDEX_TYPE` varchar(16) NOT NULL default '',
  `COMMENT` varchar(16) default NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
SHOW COLUMNS FROM information_schema.STATISTICS;
Field	Type	Null	Key	Default	Extra
TABLE_CATALOG	varchar(512)	YES		NULL	
TABLE_SCHEMA	varchar(64)	NO			
TABLE_NAME	varchar(64)	NO			
NON_UNIQUE	bigint(1)	NO		0	
INDEX_SCHEMA	varchar(64)	NO			
INDEX_NAME	varchar(64)	NO			
SEQ_IN_INDEX	bigint(2)	NO		0	
COLUMN_NAME	varchar(64)	NO			
COLLATION	varchar(1)	YES		NULL	
CARDINALITY	bigint(21)	YES		NULL	
SUB_PART	bigint(3)	YES		NULL	
PACKED	varchar(10)	YES		NULL	
NULLABLE	varchar(3)	NO			
INDEX_TYPE	varchar(16)	NO			
COMMENT	varchar(16)	YES		NULL	
SELECT table_catalog, table_schema, table_name, index_schema, index_name
FROM information_schema.statistics WHERE table_catalog IS NOT NULL;
table_catalog	table_schema	table_name	index_schema	index_name
####################################################################################
# Testcase 3.2.14.2 + 3.2.14.3: INFORMATION_SCHEMA.STATISTICS accessible information
####################################################################################
DROP DATABASE IF EXISTS db_datadict;
DROP DATABASE IF EXISTS db_datadict_2;
CREATE DATABASE db_datadict;
CREATE DATABASE db_datadict_2;
DROP   USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';
DROP   USER 'testuser2'@'localhost';
CREATE USER 'testuser2'@'localhost';
CREATE TABLE db_datadict.t1
(f1 INT NOT NULL, PRIMARY KEY(f1), f2 INT, INDEX f2_ind(f2))
ENGINE = <engine_type>;
CREATE TABLE db_datadict.t2
(f1 INT NOT NULL, PRIMARY KEY(f1), f2 INT, INDEX f2_ind(f2))
ENGINE = <engine_type>;
CREATE TABLE db_datadict_2.t3
(f1 INT NOT NULL, f2 INT, f5 DATE,
PRIMARY KEY(f1), INDEX f2f1_ind(f2,f1), UNIQUE(f5))
ENGINE = MEMORY;
CREATE TABLE db_datadict_2.t4
(f1 INT NOT NULL, PRIMARY KEY(f1), f2 INT, INDEX f2_ind(f2))
ENGINE = MEMORY;
SELECT * FROM information_schema.statistics
WHERE table_schema LIKE 'db_datadict%'
ORDER BY table_schema,table_name,index_name,seq_in_index,column_name;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	NON_UNIQUE	INDEX_SCHEMA	INDEX_NAME	SEQ_IN_INDEX	COLUMN_NAME	COLLATION	CARDINALITY	SUB_PART	PACKED	NULLABLE	INDEX_TYPE	COMMENT
NULL	db_datadict	t1	1	db_datadict	f2_ind	1	f2	NULL	0	NULL	NULL	YES	HASH	
NULL	db_datadict	t1	0	db_datadict	PRIMARY	1	f1	NULL	0	NULL	NULL		HASH	
NULL	db_datadict	t2	1	db_datadict	f2_ind	1	f2	NULL	0	NULL	NULL	YES	HASH	
NULL	db_datadict	t2	0	db_datadict	PRIMARY	1	f1	NULL	0	NULL	NULL		HASH	
NULL	db_datadict_2	t3	1	db_datadict_2	f2f1_ind	1	f2	NULL	NULL	NULL	NULL	YES	HASH	
NULL	db_datadict_2	t3	1	db_datadict_2	f2f1_ind	2	f1	NULL	0	NULL	NULL		HASH	
NULL	db_datadict_2	t3	0	db_datadict_2	f5	1	f5	NULL	0	NULL	NULL	YES	HASH	
NULL	db_datadict_2	t3	0	db_datadict_2	PRIMARY	1	f1	NULL	0	NULL	NULL		HASH	
NULL	db_datadict_2	t4	1	db_datadict_2	f2_ind	1	f2	NULL	0	NULL	NULL	YES	HASH	
NULL	db_datadict_2	t4	0	db_datadict_2	PRIMARY	1	f1	NULL	0	NULL	NULL		HASH	
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
SHOW GRANTS FOR 'testuser2'@'localhost';
Grants for testuser2@localhost
GRANT USAGE ON *.* TO 'testuser2'@'localhost'
# Establish connection testuser1 (user=testuser1)
SELECT * FROM information_schema.statistics
WHERE table_schema LIKE 'db_datadict%'
ORDER BY table_schema,table_name,index_name,seq_in_index,column_name;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	NON_UNIQUE	INDEX_SCHEMA	INDEX_NAME	SEQ_IN_INDEX	COLUMN_NAME	COLLATION	CARDINALITY	SUB_PART	PACKED	NULLABLE	INDEX_TYPE	COMMENT
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
SHOW GRANTS FOR 'testuser2'@'localhost';
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'mysql'
# Establish connection testuser2 (user=testuser2)
SELECT * FROM information_schema.statistics
WHERE table_schema LIKE 'db_datadict%'
ORDER BY table_schema,table_name,index_name,seq_in_index,column_name;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	NON_UNIQUE	INDEX_SCHEMA	INDEX_NAME	SEQ_IN_INDEX	COLUMN_NAME	COLLATION	CARDINALITY	SUB_PART	PACKED	NULLABLE	INDEX_TYPE	COMMENT
SHOW GRANTS FOR 'testuser1'@'localhost';
ERROR 42000: Access denied for user 'testuser2'@'localhost' to database 'mysql'
SHOW GRANTS FOR 'testuser2'@'localhost';
Grants for testuser2@localhost
GRANT USAGE ON *.* TO 'testuser2'@'localhost'
# Switch to connection default
GRANT SELECT ON db_datadict.t1 TO 'testuser1'@'localhost' WITH GRANT OPTION;
GRANT SELECT(f1,f5) ON db_datadict_2.t3 TO 'testuser1'@'localhost';
SELECT * FROM information_schema.statistics
WHERE table_schema LIKE 'db_datadict%'
ORDER BY table_schema,table_name,index_name,seq_in_index,column_name;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	NON_UNIQUE	INDEX_SCHEMA	INDEX_NAME	SEQ_IN_INDEX	COLUMN_NAME	COLLATION	CARDINALITY	SUB_PART	PACKED	NULLABLE	INDEX_TYPE	COMMENT
NULL	db_datadict	t1	1	db_datadict	f2_ind	1	f2	NULL	0	NULL	NULL	YES	HASH	
NULL	db_datadict	t1	0	db_datadict	PRIMARY	1	f1	NULL	0	NULL	NULL		HASH	
NULL	db_datadict	t2	1	db_datadict	f2_ind	1	f2	NULL	0	NULL	NULL	YES	HASH	
NULL	db_datadict	t2	0	db_datadict	PRIMARY	1	f1	NULL	0	NULL	NULL		HASH	
NULL	db_datadict_2	t3	1	db_datadict_2	f2f1_ind	1	f2	NULL	NULL	NULL	NULL	YES	HASH	
NULL	db_datadict_2	t3	1	db_datadict_2	f2f1_ind	2	f1	NULL	0	NULL	NULL		HASH	
NULL	db_datadict_2	t3	0	db_datadict_2	f5	1	f5	NULL	0	NULL	NULL	YES	HASH	
NULL	db_datadict_2	t3	0	db_datadict_2	PRIMARY	1	f1	NULL	0	NULL	NULL		HASH	
NULL	db_datadict_2	t4	1	db_datadict_2	f2_ind	1	f2	NULL	0	NULL	NULL	YES	HASH	
NULL	db_datadict_2	t4	0	db_datadict_2	PRIMARY	1	f1	NULL	0	NULL	NULL		HASH	
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
GRANT SELECT (f5, f1) ON `db_datadict_2`.`t3` TO 'testuser1'@'localhost'
GRANT SELECT ON `db_datadict`.`t1` TO 'testuser1'@'localhost' WITH GRANT OPTION
SHOW GRANTS FOR 'testuser2'@'localhost';
Grants for testuser2@localhost
GRANT USAGE ON *.* TO 'testuser2'@'localhost'
# Switch to connection testuser1
SELECT * FROM information_schema.statistics
WHERE table_schema LIKE 'db_datadict%'
ORDER BY table_schema,table_name,index_name,seq_in_index,column_name;