mysql innodb:describe表不显示列引用，它们显示了什么?

问题描述

CREATE TABLE accounts (
 account_name      VARCHAR(100) NOT NULL PRIMARY KEY
);

CREATE TABLE products (
 product_id        INTEGER NOT NULL PRIMARY KEY,
 product_name      VARCHAR(100)
);

CREATE TABLE bugs (
  bug_id            INTEGER NOT NULL PRIMARY KEY,
  bug_description   VARCHAR(100),
  bug_status        VARCHAR(20),
  reported_by       VARCHAR(100) REFERENCES accounts(account_name),
  assigned_to       VARCHAR(100) REFERENCES accounts(account_name),
  verified_by       VARCHAR(100) REFERENCES accounts(account_name)
 );

CREATE TABLE bugs_products (
  bug_id            INTEGER NOT NULL REFERENCES bugs,
  product_id        INTEGER NOT NULL REFERENCES products,
  PRIMARY KEY       (bug_id, product_id)
);

如果我执行描述bugs_products"，则会得到:

if i execute 'describe bugs_products' i get:

 Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| bug_id     | int(11) | NO   | PRI | NULL    |       | 
| product_id | int(11) | NO   | PRI | NULL    |       | 
+------------+---------+------+-----+---------+-------+

我还如何获得参考信息?

how can i also get references information?

推荐答案

在测试中，未使用以下语法在我的计算机上创建外键:

On testing, the foreign keys are not created on my machine using this syntax:

CREATE TABLE bugs (
  ...
  reported_by       VARCHAR(100) REFERENCES accounts(account_name),
  ...
 ) ENGINE = INNODB;

但是它们是我使用以下create语句的时候:

But they are when I use this create statement:

CREATE TABLE bugs (
  ...
  reported_by       VARCHAR(100),
  ...
  FOREIGN KEY (reported_by) REFERENCES accounts(account_name)
 ) ENGINE = INNODB;

查看表中是否存在外键的简单方法是:

An easy way to see if foreign keys exist on a table is:

show create table bugs_products

或者您可以查询信息模式:

Or you can query the information schema:

select
  table_schema
, table_name
, column_name
, referenced_table_schema
, referenced_table_name
, referenced_column_name
from information_schema.KEY_COLUMN_USAGE
where table_name = 'bugs'

还要检查您是否正在使用InnoDB存储引擎. MyISAM引擎不支持外键.您可以找到类似的引擎:

Also check you're using the InnoDB storage engine. The MyISAM engine does not support foreign keys. You can find the engine like:

select table_schema, table_name, engine
from information_schema.TABLES
where table_name = 'bugs'

如果您尝试在MyISAM表上创建外键，它将无提示地丢弃引用并假装成功.

If you try to create a foreign key on a MyISAM table, it will silently discard the references and pretend to succeed.

这篇关于mysql innodb:describe表不显示列引用，它们显示了什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！