mysql

Import Table Structures
export tables.csv:
export_tables.sh
1
mysql -uroot -p your_password -hlocalhost --batch --raw <<-EOF > tables.csv
2
select 'public' as table_schema,
3
       col.table_name,
4
       col.column_name,
5
       col.ordinal_position,
6
       col.data_type as column_type,
7
       case when (group_concat(constraint_type separator ', '))
8
                  like '%PRIMARY KEY%'
9
            then 't' else 'f' end as primary_key,
10
       case when col.is_nullable = 'YES' then 't' else 'f' end as is_nullable,
11
       tab.table_comment,
12
       col.column_comment
13
from information_schema.columns col
14
join information_schema.tables tab
15
     on col.table_schema = tab.table_schema
16
     and col.table_name = tab.table_name
17
     and tab.table_type = 'BASE TABLE'
18
left join information_schema.key_column_usage kcu
19
     on col.table_schema = kcu.table_schema
20
     and col.table_name = kcu.table_name
21
     and col.column_name = kcu.column_name
22
left join information_schema.table_constraints tco
23
     on kcu.constraint_schema = tco.constraint_schema
24
     and kcu.constraint_name = tco.constraint_name
25
     and kcu.table_name = tco.table_name
26
where col.table_schema = 'your_database_name' and col.table_schema not in('information_schema', 'sys',
27
                              'performance_schema', 'mysql')
28
group by 1,2,3,4,5,7,8,9
29
order by col.table_schema,
30
         col.table_name,
31
         col.ordinal_position
32
EOF
Copied!
 You need to replace your_database_name & password & host & username
If there are foreign keys in your database schema, you can export it：
export_relations.sh
1
mysql -uroot --batch --raw <<-EOF > relations.csv
2
SELECT 'public' AS 'schema',
3
       kcu.referenced_table_name AS 'table',
4
       kcu.referenced_column_name AS 'column',
5
       kcu.ordinal_position AS no,
6
       'public' AS relation_table_schema,
7
       kcu.table_name AS relation_table,
8
       kcu.column_name AS relation_column
9
  FROM information_schema.key_column_usage AS kcu
10
 WHERE referenced_column_name IS NOT NULL 
11
       AND CONSTRAINT_SCHEMA = 'your_database_name'
12
EOF
Copied!
 You need to replace your_database_name & password & host & username
If there are not any foreign keys in your database schema,  you don't need to import relations csv file.
You can use `auto draw`  feature to detect relationship. It has rule:
1
users.account_id -> accouts.id
Copied!
and check the AUTO DRAW checkbox:
result:

Last modified 1yr ago

Copy link