数据库字典生成

php生成mysql数据库的数据字典

1. /**
2. * 生成mysql数据字典
3. */
4. header("Content-type: text/html; charset=utf-8");
5. //配置数据库
6. $dbserver = "127.0.0.1";
7. $dbusername = "root";
8. $dbpassword = "";
9. $database = "think_team";
11. //其他配置
12. $mysql_conn = @mysql_connect("$dbserver", "$dbusername", "$dbpassword") or die("Mysql connect is error.");
13. mysql_select_db($database, $mysql_conn);
14. mysql_query('SET NAMES utf8', $mysql_conn);
15. $table_result = mysql_query('show tables', $mysql_conn);
17. $no_show_table = array(); //不需要显示的表
18. $no_show_field = array(); //不需要显示的字段
20. //取得所有的表名
21. while($row = mysql_fetch_array($table_result)){
22. if(!in_array($row[0],$no_show_table)){
23. $tables[]['TABLE_NAME'] = $row[0];
24. }
25. }
26. //替换所以表的表前缀
27. if($_GET['prefix']){
28. $prefix = 'czzj';
29. foreach($tables as $key => $val){
30. $tableName = $val['TABLE_NAME'];
31. $string = explode('_',$tableName);
32. if($string[0] != $prefix){
33. $string[0] = $prefix;
34. $newTableName = implode('_', $string);
35. mysql_query('rename table '.$tableName.' TO '.$newTableName);
36. }
37. }
38. echo "替换成功！";exit();
39. }
41. //循环取得所有表的备注及表中列消息
42. foreach ($tables as $k=>$v) {
43. $sql = 'SELECT * FROM ';
44. $sql .= 'INFORMATION_SCHEMA.TABLES ';
45. $sql .= 'WHERE ';
46. $sql .= "table_name = '{$v['TABLE_NAME']}' AND table_schema = '{$database}'";
47. $table_result = mysql_query($sql, $mysql_conn);
48. while ($t = mysql_fetch_array($table_result) ) {
49. $tables[$k]['TABLE_COMMENT'] = $t['TABLE_COMMENT'];
50. }
52. $sql = 'SELECT * FROM ';
53. $sql .= 'INFORMATION_SCHEMA.COLUMNS ';
54. $sql .= 'WHERE ';
55. $sql .= "table_name = '{$v['TABLE_NAME']}' AND table_schema = '{$database}'";
57. $fields = array();
58. $field_result = mysql_query($sql, $mysql_conn);
59. while ($t = mysql_fetch_array($field_result) ) {
60. $fields[] = $t;
61. }
62. $tables[$k]['COLUMN'] = $fields;
63. }
64. mysql_close($mysql_conn);
67. $html = '';
68. //循环所有表
69. foreach ($tables as $k=>$v) {
70. $html .= '

    ' . ($k + 1) . '、' . $v['TABLE_COMMENT'] .' （'. $v['TABLE_NAME']. '）

    '."\n";
71. $html .= ' '."\n";
72. $html .= '
'."\n";
73. $html .= '
'."\n";
74. $html .= '
'."\n";
75. $html .= '
'."\n";
76. $html .= '
'."\n";
77. $html .= '
'."\n";
78. $html .= '
'."\n";
79. $html .= '
'."\n";
80. $html .= '
'."\n";
82. foreach ($v['COLUMN'] as $f) {
83. if(!is_array($no_show_field[$v['TABLE_NAME']])){
84. $no_show_field[$v['TABLE_NAME']] = array();
85. }
86. if(!in_array($f['COLUMN_NAME'],$no_show_field[$v['TABLE_NAME']])){
87. $html .= '
'."\n";
88. $html .= '
'."\n";
89. $html .= '
'."\n";
90. $html .= '
'."\n";
91. $html .= '
'."\n";
92. $html .= '
'."\n";
93. $html .= '
'."\n";
94. $html .= '
'."\n";
95. }
96. }
97. $html .= '
'."\n";
98. $html .= '

字段名	数据类型	默认值	允许非空	自动递增	备注
' . $f['COLUMN_NAME'] . '	' . $f['COLUMN_TYPE'] . '	' . $f['COLUMN_DEFAULT'] . '	' . $f['IS_NULLABLE'] . '	' . ($f['EXTRA']=='auto_increment'?'是':' ') . '	' . $f['COLUMN_COMMENT'] . '

'."\n";
99. }
100. ?>

复制代码

13. 腾速科技有限公司数据库数据字典生成代码

复制代码