不是第一

mysql 查找数据库最大的表

  1. 查询数据库信息
  2. 切换到information_schema库
    1. use information_schema;
  3. 查看库中所有数据表及数据表描述
    1. show tables;
    2. desc tables;
  4. 查询数据库yourDataBaseName中数据表（根据行数倒序）
select table_name,table_rows,tables.* from tables WHERE TABLE_SCHEMA = 'yourDataBaseName' order by table_rows desc limit 100;

搜索关键字

  • 创建函数

getDataByDbName() 获取每个表的每一个字段是否包含关键词

CREATE DEFINER=`root`@`%` PROCEDURE `getDataByDbName`(in dbName VARCHAR(255),IN ziduan VARCHAR(255))
BEGIN
DECLARE num INT;
SET @STMT =CONCAT("SELECT COUNT(*) FROM ",dbName," WHERE `",ziduan,"` LIKE BINARY '%关键词%' INTO @num;");
PREPARE STMT FROM @STMT;
EXECUTE STMT;
IF(@num>0) THEN
    INSERT INTO temp VALUES (dbName,ziduan);
END IF;
end

exeProcesstor 执行此函数，将在数据库的 temp 表中显示字段和表名称

CREATE DEFINER=`root`@`%` PROCEDURE `exeProcesstor`()
BEGIN
DECLARE done BOOLEAN DEFAULT 0;
DECLARE biao VARCHAR (255);
DECLARE ziduan VARCHAR (255);
DECLARE indexss CURSOR
FOR
SELECT TABLE_NAME,COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='xsxc_bak' ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
OPEN indexss;
repeat
FETCH indexss INTO biao,ziduan;
call  getDataByDbName(biao,ziduan);
UNTIL done END repeat;
CLOSE indexss;
end

数据临时表

CREATE TABLE `temp` (
  `tablename` varchar(255) DEFAULT NULL,
  `LieName` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

外键约束

-- 查询外键约束
select @@Foreign_key_checks;
-- 开启外键约束
set Foreign_key_checks =1;
-- 关闭外键约束
set Foreign_key_checks =0;