How to find all the relations between all mysql ta

2019-01-11 10:33发布

How to find all the relations between all MySQL tables? If for example, I want to know the relation of tables in a database of having around 100 tables.

Is there anyway to know this?

8条回答
ゆ 、 Hurt°
2楼-- · 2019-01-11 10:55

you can use:

SHOW CREATE TABLE table_name;
查看更多
贪生不怕死
3楼-- · 2019-01-11 10:57

Try

SELECT
`TABLE_NAME`,
`COLUMN_NAME`,
`REFERENCED_TABLE_NAME`,
`REFERENCED_COLUMN_NAME`
FROM `information_schema`.`KEY_COLUMN_USAGE`
WHERE `CONSTRAINT_SCHEMA` = 'YOUR_DATABASE_NAME' AND
`REFERENCED_TABLE_SCHEMA` IS NOT NULL AND
`REFERENCED_TABLE_NAME` IS NOT NULL AND
`REFERENCED_COLUMN_NAME` IS NOT NULL

do not forget to replace YOUR_DATABASE_NAME with your database name!

查看更多
The star\"
4楼-- · 2019-01-11 11:02

Try this:

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS;
查看更多
Rolldiameter
5楼-- · 2019-01-11 11:03

The better way, programmatically speaking, is gathering data from INFORMATION_SCHEMA.KEY_COLUMN_USAGE table as follows:

SELECT 
  `TABLE_SCHEMA`,                          -- Foreign key schema
  `TABLE_NAME`,                            -- Foreign key table
  `COLUMN_NAME`,                           -- Foreign key column
  `REFERENCED_TABLE_SCHEMA`,               -- Origin key schema
  `REFERENCED_TABLE_NAME`,                 -- Origin key table
  `REFERENCED_COLUMN_NAME`                 -- Origin key column
FROM
  `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`  -- Will fail if user don't have privilege
WHERE
  `TABLE_SCHEMA` = SCHEMA()                -- Detect current schema in USE 
  AND `REFERENCED_TABLE_NAME` IS NOT NULL; -- Only tables with foreign keys

There are more columns info like ORDINAL_POSITION that could be useful depending your purpose.

More info: http://dev.mysql.com/doc/refman/5.1/en/key-column-usage-table.html

查看更多
霸刀☆藐视天下
6楼-- · 2019-01-11 11:03

SELECT 
    count(1) totalrelationships ,
    c.table_name tablename,
    CONCAT(' ',GROUP_CONCAT(c.column_name ORDER BY ordinal_position SEPARATOR ', ')) columnname,
    CONCAT(' ',GROUP_CONCAT(c.column_type ORDER BY ordinal_position SEPARATOR ', ')) columntype    
FROM
    information_schema.columns c RIGHT JOIN
    (SELECT column_name , column_type FROM information_schema.columns WHERE 
    -- column_key in ('PRI','MUL') AND  -- uncomment this line if you want to see relations only with indexes
    table_schema = DATABASE() AND table_name = 'YourTableName') AS p
    USING (column_name,column_type)
WHERE
    c.table_schema = DATABASE()
    -- AND c.table_name != 'YourTableName'
    GROUP BY tablename
    -- HAVING (locate(' YourColumnName',columnname) > 0) -- uncomment this line to search for specific column 
    ORDER BY totalrelationships desc, columnname
;
查看更多
冷血范
7楼-- · 2019-01-11 11:08

One option is : You can do reverse engineering to understand it in diagrammatic way.

When you install MySQL, you will get MySQLWorkbench. You need to open it and choose the database you want to reverse engineer. Click on Reverse Engineer option somewhere you find under the tools or Database menu. It will ask you to choose the tables. Either you select the tables you want to understand or choose the entire DB. It will generate a diagram with relationships.

查看更多
登录 后发表回答