· 5 years ago · Nov 23, 2020, 10:26 AM
1-- Index for 1 column
2
3DROP PROCEDURE IF EXISTS add_index_for_one_column_if_not_exists;
4
5DELIMITER //
6
7CREATE PROCEDURE add_index_for_one_column_if_not_exists(
8 IN target_database_name VARCHAR(100),
9 IN target_table_name VARCHAR(100),
10 IN target_column_name VARCHAR(100),
11 IN target_constraint_name VARCHAR(100)
12)
13BEGIN
14IF NOT EXISTS(
15 SELECT 1
16 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
17 WHERE
18 TABLE_NAME = target_table_name
19 AND TABLE_SCHEMA = target_table_name
20 AND CONSTRAINT_NAME = target_constraint_name)
21THEN
22 SET @sql := CONCAT('ALTER TABLE `',
23 target_database_name.target_table_name,
24 '` ADD INDEX `',
25 target_constraint_name,
26 '` (`',
27 target_column_name,
28 '`)');
29-- SELECT @sql;
30 PREPARE prepared_statement FROM @sql;
31 EXECUTE prepared_statement;
32 DROP PREPARE prepared_statement;
33END IF;
34END;
35//
36
37DELIMITER ;
38
39-- Index for 2 columns
40
41DROP PROCEDURE IF EXISTS add_index_for_two_columns_if_not_exists;
42
43DELIMITER //
44
45CREATE PROCEDURE add_index_for_two_columns_if_not_exists(
46 IN target_database_name VARCHAR(100),
47 IN target_table_name VARCHAR(100),
48 IN first_target_column_name VARCHAR(100),
49 IN second_target_column_name VARCHAR(100),
50 IN target_constraint_name VARCHAR(100)
51)
52BEGIN
53IF NOT EXISTS(
54 SELECT 1
55 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
56 WHERE
57 TABLE_NAME = target_table_name
58 AND TABLE_SCHEMA = target_table_name
59 AND CONSTRAINT_NAME = target_constraint_name)
60THEN
61 SET @sql := CONCAT('ALTER TABLE `',
62 target_database_name.target_table_name,
63 '` ADD INDEX `',
64 target_constraint_name,
65 '` (`',
66 first_target_column_name,
67 second_target_column_name,
68 '`)');
69-- SELECT @sql;
70 PREPARE prepared_statement FROM @sql;
71 EXECUTE prepared_statement;
72 DROP PREPARE prepared_statement;
73END IF;
74END;
75//
76
77DELIMITER ;
78
79-- Unique Index for 1 column
80
81DROP PROCEDURE IF EXISTS add_unique_index_for_one_column_if_not_exists;
82
83DELIMITER //
84
85CREATE PROCEDURE add_unique_index_for_one_column_if_not_exists(
86 IN target_database_name VARCHAR(100),
87 IN target_table_name VARCHAR(100),
88 IN target_column_name VARCHAR(100),
89 IN target_constraint_name VARCHAR(100)
90)
91BEGIN
92IF NOT EXISTS(
93 SELECT 1
94 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
95 WHERE
96 TABLE_NAME = target_table_name
97 AND TABLE_SCHEMA = target_table_name
98 AND CONSTRAINT_TYPE = "UNIQUE"
99 AND CONSTRAINT_NAME = target_constraint_name)
100THEN
101 SET @sql := CONCAT('ALTER TABLE `',
102 target_database_name.target_table_name,
103 '` ADD UNIQUE INDEX `',
104 target_constraint_name,
105 '` (`',
106 target_column_name,
107 '`)');
108-- SELECT @sql;
109 PREPARE prepared_statement FROM @sql;
110 EXECUTE prepared_statement;
111 DROP PREPARE prepared_statement;
112END IF;
113END;
114//
115
116DELIMITER ;
117
118-- Unique Index for 2 columns
119
120DROP PROCEDURE IF EXISTS add_unique_index_for_two_columns_if_not_exists;
121
122DELIMITER //
123
124CREATE PROCEDURE add_unique_index_for_two_columns_if_not_exists(
125 IN target_database_name VARCHAR(100),
126 IN target_table_name VARCHAR(100),
127 IN first_target_column_name VARCHAR(100),
128 IN second_target_column_name VARCHAR(100),
129 IN target_constraint_name VARCHAR(100)
130)
131BEGIN
132IF NOT EXISTS(
133 SELECT 1
134 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
135 WHERE
136 TABLE_NAME = target_table_name
137 AND TABLE_SCHEMA = target_table_name
138 AND CONSTRAINT_TYPE = "UNIQUE"
139 AND CONSTRAINT_NAME = target_constraint_name)
140THEN
141 SET @sql := CONCAT('ALTER TABLE `',
142 target_database_name.target_table_name,
143 '` ADD UNIQUE INDEX `',
144 target_constraint_name,
145 '` (`',
146 first_target_column_name,
147 second_target_column_name,
148 '`)');
149-- SELECT @sql;
150 PREPARE prepared_statement FROM @sql;
151 EXECUTE prepared_statement;
152 DROP PREPARE prepared_statement;
153END IF;
154END;
155//
156
157DELIMITER ;
158
159
160