1. 首页
  2. 文章中心
  3. sql

sql函数 批量修改数据表——自定义表名前提第一步批量创建表第二步 筛选出需要的数据第三步 最终形态，利用数据表传递更多的参数

107 阅读 0 评论
我是靠谱客的博主 含蓄纸飞机，这篇文章主要介绍sql函数 批量修改数据表——自定义表名前提第一步批量创建表第二步 筛选出需要的数据第三步 最终形态，利用数据表传递更多的参数，现在分享给大家，希望可以做个参考。

文章目录

  • 前提
  • 第一步批量创建表
    • 根据给定表名创建表结构
  • 第二步 筛选出需要的数据
  • 第三步 最终形态，利用数据表传递更多的参数
    • 1、先建传参的数据表
    • 2、给变量传值
    • 3、读取数据表中的类容
    • 具体写法

本人也是初学者，本文可能存在很多漏洞欢迎指出；相信在各路大佬的帮助下，我才能学会更多。

前提

  • 设计目的：1、给数据库释放空间；2、要将需要的数据单独保存出来
  • 备注：由于delete删除数据并不能立即释放空间，truncat又不能跟筛选条件，drop删表又是万万不能的
  • 设计思路：先将要保存的数据给保存到一张new表上面，将原来的old表删除，再将new数据表名改成old

第一步批量创建表

根据给定表名创建表结构

参考：sql 批量创建表

参考：将一个表的数据复制到另外一个表

复制代码
1
2
CREATE TABLE table4 LIKE table1

提示：由于给定变量为字符串且是varchar类型，最大字节长度就是255,所以在给参数时一定要注意
后面会创建一个数据表来传参，以此来改善这个弊端

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
DELIMITER $$

DROP PROCEDURE IF EXISTS Only_one$$

CREATE PROCEDURE Only_one(tmp VARCHAR(255))  -- 要注意这里参数不能过长
BEGIN 
DECLARE
	i INT;
DECLARE
	Start1 INT;
DECLARE
	Length INT;
DECLARE
	TotalLenght INT;
DECLARE
	filed VARCHAR ( 100 );
DECLARE
	mmm VARCHAR ( 100 );	
	
DECLARE
	sqlStr VARCHAR ( 2000 );
DECLARE
	stmtNovelSearch VARCHAR ( 2000 );  -- 其实这里不用定义也不会报错
	
SET i = 1;
SET Start1 = 1;
SET Length = 0;	
	
SET TotalLenght=length(tmp);

WHILE i<=TotalLenght DO
	IF (SUBSTRING(tmp,i,1)=',')
	THEN

		SET filed = SUBSTRING( tmp, Start1, Length );-- 从start1开始截取length长度的字符
		SET mmm=CONCAT(filed,"_old");
		
	
		SET @sqlStr = CONCAT( 'CREATE TABLE ', mmm, ' SELECT * FROM ',filed,' LIMIT 5' );-- 拼装sql语句
		PREPARE stmtNovelSearch 
		FROM
			@sqlStr;-- prepare 预编译必须采用这种传参方式
		EXECUTE stmtNovelSearch;
		DEALLOCATE PREPARE stmtNovelSearch;
		
		SET Start1 = i + 1;
		
		SET Length = 0;
	ELSE 
		SET Length = Length + 1;-- 如果不是， 则截取的长度加1
		
	END IF;
	
	SET i = i + 1;
	
	
END WHILE;

END $$

call Only_one('smbms_bill,smbms_role,smbms_user,smbms_address,smbms_provider,');  -- 用的逗号截取的表名，所以最后一定要加个逗号
DELIMITER ;

第二步 筛选出需要的数据

假如只要最近十天的数据

复制代码
1
2
 WHERE DATE_SUB(CURDATE(), INTERVAL 10 DAY) <= create_time

处于对生产数据的保护，我这里还是拷贝数据的方法

复制代码
1
2
CREATE TABLE New_table SELECT * FROM Old_table  LIMIT 5

具体sql

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
DELIMITER $$

DROP PROCEDURE IF EXISTS Only_one$$

CREATE PROCEDURE Only_one(tmp VARCHAR(255))
BEGIN 
DECLARE
	i INT;
DECLARE
	Start1 INT;
DECLARE
	Length INT;
DECLARE
	TotalLenght INT;
DECLARE
	filed VARCHAR ( 100 );
DECLARE
	mmm VARCHAR ( 100 );	
	
DECLARE
	sqlStr VARCHAR ( 2000 );
DECLARE
	sqlStr2 VARCHAR ( 2000 );
DECLARE
	sqlStr3 VARCHAR ( 2000 );	
DECLARE
	stmtNovelSearch VARCHAR ( 2000 );
	

SET i = 1;
SET Start1 = 1;
SET Length = 0;	
	
SET TotalLenght=length(tmp);

WHILE i<=TotalLenght DO
	IF (SUBSTRING(tmp,i,1)=',')
	THEN

		SET filed = SUBSTRING( tmp, Start1, Length );-- 从start1开始截取length长度的字符
		SET mmm=CONCAT(filed,"_old");
		
	
		SET @sqlStr = CONCAT( 'CREATE TABLE ', mmm, ' SELECT * FROM ',filed,' LIMIT 5' );-- 截取要保存的数据
		PREPARE stmtNovelSearch 
		FROM
			@sqlStr;-- prepare 必须采用这种传参方式
		EXECUTE stmtNovelSearch;
		DEALLOCATE PREPARE stmtNovelSearch;
		
		SET @sqlStr2 = CONCAT( 'DROP TABLE ', filed );-- 删除原来的表
		PREPARE stm1 
		FROM
			@sqlStr2;-- prepare 必须采用这种传参方式
		EXECUTE stm1;
		DEALLOCATE PREPARE stm1;
		
		SET @sqlStr3 = CONCAT( 'ALTER TABLE ', mmm ,' RENAME TO ' ,filed );-- 修改数据表的名字
		PREPARE stm2 
		FROM
			@sqlStr3;-- prepare 必须采用这种传参方式
		EXECUTE stm2;
		DEALLOCATE PREPARE stm2;	
		
		
		SET Start1 = i + 1;
		
		SET Length = 0;
	ELSE 
		SET Length = Length + 1;-- 如果不是， 则截取的长度加1
		
	END IF;
	
	SET i = i + 1;
	
	
END WHILE;

END $$

call Only_one('smbms_bill,smbms_role,smbms_user,smbms_address,smbms_provider,');

DELIMITER ;

第三步 最终形态，利用数据表传递更多的参数

先要理解以下几个sql 的用法

1、先建传参的数据表

复制代码
1
2
3
4
5
6
DROP TABLE IF EXISTS `one`;
CREATE TABLE `one`  (
  `ID` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `NAME` varchar(255) 
)

将需要的参数放进去
在这里插入图片描述

2、给变量传值

参考：Mysql存储过程查询结果赋值到变量的方法

不知道处于什么原因我只能用方法三，前面两种总是要报错

复制代码
1
2
3
select count(*) into @cnt1 from test_tbl;
select @cnt1;

3、读取数据表中的类容

复制代码
1
2
select NAME into New_table from `one` LIMIT 从第几行读,1;

具体写法

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
DELIMITER $$

DROP PROCEDURE IF EXISTS Only_one$$

CREATE PROCEDURE Only_one()
BEGIN 
DECLARE
	i INT;
DECLARE
	mmm VARCHAR ( 100 );	
	
DECLARE
	sqlStr VARCHAR ( 2000 );
DECLARE
	sqlStr2 VARCHAR ( 2000 );
DECLARE
	sqlStr3 VARCHAR ( 2000 );	
	

SET i = 0;
	
select COUNT(*) into @num from `one` ;  -- one数据表名
select @num;
WHILE i<@num DO

    select NAME into @cnt from `one` LIMIT i,1;  -- 从数据表中读取要修改的字段

    select @cnt;
		SET mmm=CONCAT(@cnt,"_old");
		
		SET @sqlStr = CONCAT( 'CREATE TABLE ', mmm, ' SELECT * FROM ',@cnt,' WHERE DATE_SUB(CURDATE(), INTERVAL 10 DAY) <= create_time' );-- 拼装sql语句
		PREPARE stmtNovelSearch 
		FROM
			@sqlStr;-- prepare 必须采用这种传参方式
		EXECUTE stmtNovelSearch;
		DEALLOCATE PREPARE stmtNovelSearch;
		
		SET @sqlStr2 = CONCAT( 'DROP TABLE ', @cnt );-- 拼装sql语句
		PREPARE stm1 
		FROM
			@sqlStr2;-- prepare 必须采用这种传参方式
		EXECUTE stm1;
		DEALLOCATE PREPARE stm1;
		
		SET @sqlStr3 = CONCAT( 'ALTER TABLE ', mmm ,' RENAME TO ' ,@cnt );-- 拼装sql语句
		PREPARE stm2 
		FROM
			@sqlStr3;-- prepare 必须采用这种传参方式
		EXECUTE stm2;
		DEALLOCATE PREPARE stm2;	
			
	SET i = i + 1;
	
END WHILE;
END $$
call Only_one();
DELIMITER ;

说实话这个sql折磨我已久，希望能够帮大家

最后

以上就是含蓄纸飞机最近收集整理的关于sql函数 批量修改数据表——自定义表名前提第一步批量创建表第二步 筛选出需要的数据第三步 最终形态，利用数据表传递更多的参数的全部内容，更多相关sql函数内容请搜索靠谱客的其他文章。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(71)

相关文章

mysql2012更改表名_怎样用SQL语句修改表名与表中的列名?
mysql2012更改表名_怎样用SQL语句修改表名与表中的列名?
sql不变的情况下，如何动态替换（多表查询）SQL中的表名
sql不变的情况下，如何动态替换（多表查询）SQL中的表名
mysql批量改名字_MySQL批量修改相同后缀表名
mysql批量改名字_MySQL批量修改相同后缀表名
Mysql数据表操作
Mysql数据表操作
sql函数 批量修改数据表——自定义表名前提第一步批量创建表第二步 筛选出需要的数据第三步 最终形态，利用数据表传递更多的参数
sql函数 批量修改数据表——自定义表名前提第一步批量创建表第二步 筛选出需要的数据第三步 最终形态，利用数据表传递更多的参数
rename同时给多个表改表名
rename同时给多个表改表名
sql server(MsSql)字段命名,表命名,视图命名,SQL语句书写参考先看个辣眼睛的SQL命名规范
sql server(MsSql)字段命名,表命名,视图命名,SQL语句书写参考先看个辣眼睛的SQL命名规范
mysql列名命名_重命名MySQL中的列名？
mysql列名命名_重命名MySQL中的列名？

评论列表共有 0 条评论

立即
投稿
返回
顶部