3SxfKTSt

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