挨踢小子MySQL数据字典源码

最新推荐文章于 2024-04-27 21:13:36 发布

置顶挨踢小子

最新推荐文章于 2024-04-27 21:13:36 发布

阅读量202

点赞数

分类专栏： MYSQL

本文链接：https://blog.csdn.net/qq_37126357/article/details/83378305

版权

MYSQL 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

该工具由挨踢小子独家原创，以提高开发效率为目的，连接MySQL数据库，实时监测数据变化动态，供梳理需求和查询表结构字段。工具由PHP开发，适用于PHP运行环境之下。

效果演示：

在这里插入图片描述

源码展示

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>挨踢小子数据字典</title>
<style>
*{margin:0px;padding:0px;font-size:14px;} 
h3{line-height:50px;text-align:center;font-size:30px;color: #800000;}
.boxtab{width:60%; min-width:300px;margin:0px auto;background: #F5F5DC;}
.search-form{width: 100%;padding: 10px 0px; text-align: center;}
.myform >input{width:12%}
.myform >label{font-weight: bolder;}

h2{line-height:50px;text-align:center;font-size:20px;line-height:35px;}
.tab th{background: #3F3F3F; padding: 5px;color:#FFF; line-height:25px;}
.tab tr td{border: 1px solid #aaa;border-collapse: collapse;	line-height:30px;}

.zilist{position: fixed;left: 6px;top:10%;background: #F5F5DC;border: 1px solid #DCDCDC;padding: 10px 6px 6px 10px;    width: 16%; max-height:80% ;overflow:auto;}
.zilist a{display:block;line-height:35px;font-size:14px;color:#333;text-decoration: none;float: left;width: 48%;height:35px;overflow:hidden;text-align: center;border-bottom: 1px solid #aaa;}
.zilist a:hover{color:#3F3F3F;font-weight: bolder;}
.zilist a:nth-child(odd){border-right: 1px solid #aaa;} 

td {padding: 3px 5px 3px 10px;vertical-align: top;overflow:hidden;}
.tab tr:nth-child(odd){background:#F5F5F5;}
.tab tr:hover{background:#D2B48C;}

.colu{ max-width:150px}

.tab{width:100%;border: 1px solid #aaa;  border-collapse: collapse;}

.cor{line-height:30px;text-align:center;color: #fff;background: #3F3F3F;position: fixed;bottom: 0; width: 60%;}
.footer{ height:80px;text-align:center;width: 60%;}
.menu a{float:right;background:#F5F5DC;font-weight: bolder;text-decoration: none;color:#B22222;border:0px;border-radius:5%;margin-left: 50px;}

</style>
</head>
<body class="boxtab"> 
<div class="menu">
  <h3>挨踢小子数据字典连接工具</h3>  
  <div class="search-form">
  	 <form  class="myform"  name="myform" action="<?php echo $_SERVER['PHP_SELF'];?>" method="post"> 
      <label>IP地址：</label><input type="text" id="host" name="host" value="127.0.0.1" />
      <label>数据库：</label><input type="text" id="db" name="db" value="mysql" />
      <label>账户：</label><input type="text" id="db_user" name="db_user" value="root" />
      <label>密码：</label><input type="password" id="db_pwd" name="db_pwd" value="123456" /> 
      <button type="submit" id="submit" name="submit" value="提交">提交</button> 
    </form> 
  </div> 
  
   <a onclick="downloadInfo(this)">导出表格</a>
</div> 

<?php 
header("Content-type: text/html; charset=utf-8");
date_default_timezone_set("PRC");	
/**
* @project: 挨踢小子数据字典详细说明
* @desc: 该文件应该放置于php运行环境之下，并配置下面的数据库。在建表过程中需要注意数据表注释，数据字段注释
* @author: Aiti 
*/  

if(isset($_POST['submit'])) {
   
   if(!empty($_POST["host"])&&!empty($_POST["db"])&&!empty($_POST["db_user"])&&!empty($_POST["db_pwd"])){
   $host=trim($_POST["host"]);
   $db=trim($_POST["db"]);
   $db_user=trim($_POST["db_user"]);
   $db_pwd=trim($_POST["db_pwd"]);   
   
   session_start();   
   $_SESSION = $_POST;    
   echo "<script>document.getElementById('host').value = '".$_SESSION['host']."';
   	          document.getElementById('db').value ='".$_SESSION['db']."';
   	          document.getElementById('db_user').value ='".$_SESSION['db_user']."';
   	          document.getElementById('db_pwd').value ='".$_SESSION['db_pwd']."';  
   	        </script>";  
   }else{ 
   	echo "<script>alert('输入有误，请重新输入');</script>";
   	exit;
   }
   
$mysql_conf = array(
   'host'    => $host,
   'db'      => $db,   	//库名
   'db_user' => $db_user,	//用户名
   'db_pwd'  => $db_pwd,	//密码
);
$mysqli = @new mysqli($mysql_conf['host'], $mysql_conf['db_user'], $mysql_conf['db_pwd']);
if ($mysqli->connect_errno) {
   //弹框问题说明
   echo "<script>alert('输入有误，请重新输入');</script>"; 
   die("could not connect to the database:\n" . $mysqli->connect_error);//诊断连接错误
 
}
$mysqli->query("set names 'utf8';");//编码转化
$select_db = $mysqli->select_db($mysql_conf['db']);
if (!$select_db) {
   echo "<script>alert('数据库连接失败，请检查');</script>";
   die("could not connect to the db:\n" .  $mysqli->error);
}
//获取该数据库下面所有的表和表注释
$tablesql="Select table_name table_name,TABLE_COMMENT table_annotation from INFORMATION_SCHEMA.TABLES Where table_schema = '".$mysql_conf['db']."'";
$tableresone = $mysqli->query($tablesql);
$tablerestwo = $mysqli->query($tablesql);

echo "<ul class='zilist'>";
$newstr=''; 
while ($tablerow =$tableresone->fetch_assoc()) {
   if(empty($tablerow['table_annotation'])){
   	
   	$newstr.="<a href='#tab".$tablerow['table_name']."'>".$tablerow['table_name']."</a>";
   }else{
   	$newstr.="<a href='#tab".$tablerow['table_name']."'>".$tablerow['table_annotation']."</a>";
   }
    
} 
echo $newstr."</ul>";
while ($tablerow =$tablerestwo->fetch_assoc()) {
//    遍历数据库中的表名组装语句
   $infosql = "SELECT  
                 `COLUMN_NAME`,
                 `COLUMN_TYPE`,
                 `DATA_TYPE`,
                 `CHARACTER_MAXIMUM_LENGTH`,
                 `IS_NULLABLE`,
                 `COLUMN_DEFAULT`,
                 `COLUMN_COMMENT`   
           FROM  INFORMATION_SCHEMA.COLUMNS   
           where  table_schema ='".$mysql_conf['db']."' AND  table_name  ='{$tablerow['table_name']}'";
   $infores = $mysqli->query($infosql);
   if (!$infores) {
       die("sql error:\n" . $mysqli->error);
   }
   echo "<table class='tab'>";
   echo "<caption>";
   echo '<h2 id="tab'.$tablerow['table_name'].'">'.$tablerow['table_name']." ".$tablerow['table_annotation']."</h2>";
   echo "</caption>
               <tr>
                   <th>小驼峰</th>
                   <th>字段名</th>
                   <th>数据类型</th>
                   <th>字段类型</th>
                   <th>长度</th>
                   <th>是否为空</th>
                   <th>默认值</th>
                   <th>备注</th>
               </tr>";
   while ($inforow = $infores->fetch_assoc()) {
                           //  遍历输出表中的各项字段的信息
       echo "<tr>
       	<td>".convertUnderline($inforow['COLUMN_NAME'])."</td>
       	<td>".$inforow['COLUMN_NAME']."</td>
           <td><div class='colu'>".$inforow['COLUMN_TYPE']."</div></td>
           <td>".$inforow['DATA_TYPE']."</td>
           <td>".$inforow['CHARACTER_MAXIMUM_LENGTH']."</td>
           <td>".$inforow['IS_NULLABLE']."</td>
           <td>".$inforow['COLUMN_DEFAULT']."</td>
           <td>" .$inforow['COLUMN_COMMENT']."</td>
             </tr>";
   }
   echo "</table>";
   echo "<br>";
   $infores->free();
}
$tableresone->free();
$tablerestwo->free();
$mysqli->close();
}

//PHP 转小驼峰
function convertUnderline($str)
{
   $str = preg_replace_callback('/([-_]+([a-z]{1}))/i',function($matches){
       return strtoupper($matches[2]);
   },strtolower($str));
   return $str;
} 
?>
  
  
<div class="footer"></div>
<div class="cor">© 版权所有 2018-2019 Create By Aiti</div>
 
   <script> 
   	 
   	window.onload=function (){  
   	
   		if(document.body.scrollHeight<document.body.clientWidth){
   			document.getElementById("submit").click();
   		} 	 
   	}
   	
   	//下载数据字典详情
   	function  downloadInfo(that){
   		//获取session中的数据
   		// 使用outerHTML属性获取整个table元素的HTML代码（包括<table>标签），然后包装成一个完整的HTML文档，设置charset为urf-8以防止中文乱码
   		var html = "<html><head><meta charset='utf-8' /></head><body>";
   		//获取表格集合
   		var objs = document.getElementsByTagName("table");
   		//循环拼接
   		for(var i=0;i<objs.length;i++){ 
   		  html+=document.getElementsByTagName("table")[i].outerHTML; 
   		 }  
   		html+="</body></html>";
   		// 实例化一个Blob对象，其构造函数的第一个参数是包含文件内容的数组，第二个参数是包含文件类型属性的对象
   		var blob = new Blob([html], { type: "application/vnd.ms-excel" }); 
   		
   		// 利用URL.createObjectURL()方法为a元素生成blob URL
   		that.href = URL.createObjectURL(blob);
   		// 设置文件名
   		that.download = "挨踢小子数据字典详细说明.xls";
   	} 
       
   </script>
</body> 
</html>