is_key_column_usage.result

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

t1_my_tablex	f2
ALTER TABLE db_datadict.t1_my_tablex CHANGE COLUMN f1 first_col CHAR(12);
SELECT DISTINCT table_name, column_name FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex'
ORDER BY table_name, column_name;
table_name	column_name
t1_my_tablex	f2
t1_my_tablex	first_col
SELECT constraint_schema, constraint_name, table_schema,
table_name, column_name, ordinal_position
FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex'
ORDER BY constraint_schema, constraint_name, table_schema,
table_name, ordinal_position;
constraint_schema	constraint_name	table_schema	table_name	column_name	ordinal_position
db_datadict	PRIMARY	db_datadict	t1_my_tablex	first_col	1
db_datadict	PRIMARY	db_datadict	t1_my_tablex	f2	2
CREATE INDEX f2 ON db_datadict.t1_my_tablex(f2);
SELECT constraint_schema, constraint_name, table_schema,
table_name, column_name, ordinal_position
FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex'
ORDER BY constraint_schema, constraint_name, table_schema,
table_name, ordinal_position;
constraint_schema	constraint_name	table_schema	table_name	column_name	ordinal_position
db_datadict	PRIMARY	db_datadict	t1_my_tablex	first_col	1
db_datadict	PRIMARY	db_datadict	t1_my_tablex	f2	2
DROP INDEX f2 ON db_datadict.t1_my_tablex;
SELECT constraint_schema, constraint_name, table_schema,
table_name, column_name, ordinal_position
FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex'
ORDER BY constraint_schema, constraint_name, table_schema,
table_name, ordinal_position;
constraint_schema	constraint_name	table_schema	table_name	column_name	ordinal_position
db_datadict	PRIMARY	db_datadict	t1_my_tablex	first_col	1
db_datadict	PRIMARY	db_datadict	t1_my_tablex	f2	2
ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE (f2);
SELECT constraint_schema, constraint_name, table_schema,
table_name, column_name, ordinal_position
FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex'
ORDER BY constraint_schema, constraint_name, table_schema,
table_name, ordinal_position;
constraint_schema	constraint_name	table_schema	table_name	column_name	ordinal_position
db_datadict	f2	db_datadict	t1_my_tablex	f2	1
db_datadict	PRIMARY	db_datadict	t1_my_tablex	first_col	1
db_datadict	PRIMARY	db_datadict	t1_my_tablex	f2	2
DROP INDEX f2 ON db_datadict.t1_my_tablex;
SELECT constraint_schema, constraint_name, table_schema,
table_name, column_name, ordinal_position
FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex'
ORDER BY constraint_schema, constraint_name, table_schema,
table_name, ordinal_position;
constraint_schema	constraint_name	table_schema	table_name	column_name	ordinal_position
db_datadict	PRIMARY	db_datadict	t1_my_tablex	first_col	1
db_datadict	PRIMARY	db_datadict	t1_my_tablex	f2	2
ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE my_idx (f2);
SELECT constraint_schema, constraint_name, table_schema,
table_name, column_name, ordinal_position
FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex'
ORDER BY constraint_schema, constraint_name, table_schema,
table_name, ordinal_position;
constraint_schema	constraint_name	table_schema	table_name	column_name	ordinal_position
db_datadict	my_idx	db_datadict	t1_my_tablex	f2	1
db_datadict	PRIMARY	db_datadict	t1_my_tablex	first_col	1
db_datadict	PRIMARY	db_datadict	t1_my_tablex	f2	2
DROP INDEX my_idx ON db_datadict.t1_my_tablex;
SELECT constraint_schema, constraint_name, table_schema,
table_name, column_name, ordinal_position
FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex'
ORDER BY constraint_schema, constraint_name, table_schema,
table_name, ordinal_position;
constraint_schema	constraint_name	table_schema	table_name	column_name	ordinal_position
db_datadict	PRIMARY	db_datadict	t1_my_tablex	first_col	1
db_datadict	PRIMARY	db_datadict	t1_my_tablex	f2	2
ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE my_idx (f4,first_col);
SELECT constraint_schema, constraint_name, table_schema,
table_name, column_name, ordinal_position
FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex'
ORDER BY constraint_schema, constraint_name, table_schema,
table_name, ordinal_position;
constraint_schema	constraint_name	table_schema	table_name	column_name	ordinal_position
db_datadict	my_idx	db_datadict	t1_my_tablex	f4	1
db_datadict	my_idx	db_datadict	t1_my_tablex	first_col	2
db_datadict	PRIMARY	db_datadict	t1_my_tablex	first_col	1
db_datadict	PRIMARY	db_datadict	t1_my_tablex	f2	2
SELECT constraint_schema, constraint_name, table_schema,
table_name, column_name, ordinal_position
FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex'
ORDER BY constraint_schema, constraint_name, table_schema,
table_name, ordinal_position;
constraint_schema	constraint_name	table_schema	table_name	column_name	ordinal_position
db_datadict	my_idx	db_datadict	t1_my_tablex	f4	1
db_datadict	my_idx	db_datadict	t1_my_tablex	first_col	2
db_datadict	PRIMARY	db_datadict	t1_my_tablex	first_col	1
db_datadict	PRIMARY	db_datadict	t1_my_tablex	f2	2
ALTER TABLE db_datadict.t1_my_tablex
DROP COLUMN first_col;
SELECT constraint_schema, constraint_name, table_schema,
table_name, column_name, ordinal_position
FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex'
ORDER BY constraint_schema, constraint_name, table_schema,
table_name, ordinal_position;
constraint_schema	constraint_name	table_schema	table_name	column_name	ordinal_position
db_datadict	my_idx	db_datadict	t1_my_tablex	f4	1
db_datadict	PRIMARY	db_datadict	t1_my_tablex	f2	1
SELECT table_name, column_name
FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex'
ORDER BY table_name, column_name;
table_name	column_name
t1_my_tablex	f2
t1_my_tablex	f4
DROP TABLE db_datadict.t1_my_tablex;
SELECT table_name, column_name
FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex';
table_name	column_name
SELECT table_name FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex';
table_name
CREATE TABLE db_datadict.t1_my_tablex
ENGINE = <engine_type> AS
SELECT 1 AS f1;
SELECT table_name FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex';
table_name
ALTER TABLE db_datadict.t1_my_tablex ADD PRIMARY KEY(f1);
SELECT table_name FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex';
table_name
t1_my_tablex
SELECT table_name FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex';
table_name
t1_my_tablex
DROP DATABASE db_datadict;
SELECT table_name FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex';
table_name
########################################################################
# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
#           DDL on INFORMATION_SCHEMA table are not supported
########################################################################
DROP DATABASE IF EXISTS db_datadict;
DROP TABLE IF EXISTS db_datadict.t1;
CREATE DATABASE db_datadict;
CREATE TABLE db_datadict.t1 (f1 BIGINT)
ENGINE = <engine_type>;
INSERT INTO information_schema.key_column_usage
(constraint_schema, constraint_name, table_name)
VALUES (          'mysql',       'primary',       'db');
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
INSERT INTO information_schema.key_column_usage
SELECT * FROM information_schema.key_column_usage;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
UPDATE information_schema.key_column_usage
SET  table_name = 'db1' WHERE constraint_name = 'primary';
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DELETE FROM information_schema.key_column_usage WHERE table_name = 't1';
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
TRUNCATE information_schema.key_column_usage;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
CREATE INDEX i3 ON information_schema.key_column_usage(table_name);
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.key_column_usage ADD f1 INT;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP TABLE information_schema.key_column_usage;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.key_column_usage
RENAME db_datadict.key_column_usage;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.key_column_usage
RENAME information_schema.xkey_column_usage;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP TABLE db_datadict.t1;
DROP DATABASE db_datadict;