mysql批量创建视图，思路加实践

mysql批量创建视图及导出视图或表的结构，思路加实践

100多张表建立相应字段视图很麻烦，用如下方法：

1. 创建临时表t_tmp02，主要放入该数据库的表明和字段名和排序等字段，可以按需添加

   drop table t_tmp02; 
   
   SELECT * FROM information_schema.COLUMNS  -- 该表存放数据库表明和字段信息，可以下看一看
   
   -- 我们要求基本表字段全部大写创建对应视图，所以除原表名和字段我加了一列全部大写的字段（upper函数），ord_id字段排序需要
   create  table t_tmp02
   select table_name as table_name, column_name as table_column , upper(column_name) as Table_column1 , ORDINAL_POSITION as ord_id
   from information_schema.COLUMNS
   where table_schema='big_data_test' -- 数据库名 and  ordinal_position in(1,2)  -- 第一列或者第二列
   and table_name like 't_%'
   and table_name in ( select DISTINCT table_name from information_schema.COLUMNS where table_schema='big_data_test' and table_name like 't_%' and column_name in (  'p_code','c_code','a_code'))  --仅仅选择含有这三个字段的表
   and table_name not like '%1'  -- 排除以时间作为标识的临时表
   and table_name not like 'v%'  -- 排除以v开头的视图
   and table_name not like '%backup'; -- 排除以'bak'作为标识的临时表
   
   
   -- 查看有无忽略的表
   select distinct table_name
   from information_schema.COLUMNS
   where table_schema='big_data_test'
   and table_name like 't_%'
   and table_name not in(select table_name from t_tmp02)
   and table_name not like '%1'  -- 排除以时间作为标识的临时表
   and table_name not like 'v%'  -- 排除以v开头的视图
   and table_name not like '%backup'
   order by table_name;
   
   
   select * from t_tmp02 -- 可以在这里验证一下
   
   
   
   
   

2. 使用CONCAT函数批量创建所需创建视图的语句和为这些试图赋予权限的语句

   select CONCAT('create view common_',SUBSTR(table_name,1,length(table_name)) ,'(' , GROUP_CONCAT(Table_column1 ORDER BY ord_id),')',' as select ',GROUP_CONCAT(Table_column ORDER BY ord_id),' from big_data_test.',table_name,';')
   from t_tmp02 GROUP BY table_name ; -- 示例：create view common_t_cs_xxx(KID,SID) as select kid,sid from big_data_test.t_cs_xxx;-- GROUP_CONCAT 函数用来拼接group后的语句，内用了排序
   
   -- 赋予权限语句同理
   select CONCAT('GRANT SELECT ON big_data_test.common_',table_name,' TO "common"@"%"' ,';')
   from t_tmp02 GROUP BY table_name ;
   
   select CONCAT('GRANT SHOW VIEW ON big_data_test.common_',table_name,' TO "common"@"%"' ,';')
   from t_tmp02 GROUP BY table_name ;
   
   -- 同理还可建立删除所有视图的语句，只要有规律可循，这就是关系的魅力。
   
   

3. 对于上述语句或这次小任务过程中一些语句杂记。

1.创建用户并授权
-- 创建用户语句，创建用户后，该用户只能连接到数据库服务器，但并没有操作该数据库服务器的权限。
CREATE USER 'usernamexxx'@'hostxxx' IDENTIFIED BY 'passwordxxx';  -- hostxxx：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符% ；passwordxxx：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器
-- 授予权限，授权之后需要用户重连MySQL，才能获取相应的权限。用以下上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，自己查命令去with啥
GRANT privilegesxxx ON databasenamexxx.tablenamexxx TO 'usernamexxx'@'hostxxx' -- privilegesxxx：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL；databasenamexxx：数据库名；tablenamexxx：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*
-- 删除用户
DROP USER 'usernamexxx'@'hostxxx'; --@后可选，创建更改撤销权限等查阅mysql官网。用户详情的权限列表请参考MySQL官网说明：http://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html  -- 记得删除用户时不要从用户表或者delete那条记录直接删，删不干净的
2.导出所有的视图和表的设计结构，类似navicate
SELECT
	TABLE_NAME 表名,
	ORDINAL_POSITION 序号,
	COLUMN_NAME 字段名,
	COLUMN_TYPE 数据类型,
    IF( column_key='PRI','是','否' ) AS '主键', -- column_key='PRI' 时，为主键唯一索引，是返回“是”，否返回空
		IF( IS_NULLABLE = 'NO', '是', '否' ) AS '非空', --  IS_NULLABLE = 'NO' 时，为必填，必填返回“是”，非必填返回空
		COLUMN_DEFAULT 默认值,
	 COLUMN_COMMENT 描述
	-- CHARACTER_MAXIMUM_LENGTH 字符串最大长度【以字符为单位】,
	-- CHARACTER_OCTET_LENGTH 字符串最大长度【以字节为单位】,
	-- CHARACTER_SET_NAME 字符串字符集名称,
	-- COLLATION_NAME 字符串归类名称,
	-- NUMERIC_PRECISION 数字精度,
	-- NUMERIC_SCALE 数字刻度,
	-- DATETIME_PRECISION 时间分数秒精度
FROM
	INFORMATION_SCHEMA.COLUMNS 
	WHERE-- performance_schema 为数据库名称，到时候只需要修改成你要导出表结构的数据库即可
	table_schema = 'big_data_test'  --填写数据库名称
	AND TABLE_NAME like 't%'	
-- AND
-- events_errors_summary_by_account_by_error 为表名，到时候换成你要导出的表的名称
-- 如果不写的话，默认会查询出该数据库中所有表的表结构；这里如果指定表名，则可以导出单独一个表的表结构
-- table_name = 'events_errors_summary_by_thread_by_error'
	
ORDER BY
	TABLE_NAME,
	ORDINAL_POSITION

4.总结：这些数据涉及库表的都是查出表和列的名称进行操作，mysql数据库主要看information_schema下的一些表