SM79YMSb

1CREATE INDEX IF NOT EXISTS index_name ON table(column)
2ERROR 1064 (42000): You have an error in your SQL syntax;... 
3     
4ALTER TABLE table_name ADD INDEX (column_to_index);
5ALTER TABLE table_name ADD INDEX (column_to_index); 
6     
7mysql> show create table statisticsG
8*************************** 1. row ***************************
9       Table: STATISTICS
10Create Table: CREATE TEMPORARY TABLE `STATISTICS` (
11  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
12  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
13  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
14  `NON_UNIQUE` bigint(1) NOT NULL DEFAULT '0',
15  `INDEX_SCHEMA` varchar(64) NOT NULL DEFAULT '',
16  `INDEX_NAME` varchar(64) NOT NULL DEFAULT '',
17  `SEQ_IN_INDEX` bigint(2) NOT NULL DEFAULT '0',
18  `COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
19  `COLLATION` varchar(1) DEFAULT NULL,
20  `CARDINALITY` bigint(21) DEFAULT NULL,
21  `SUB_PART` bigint(3) DEFAULT NULL,
22  `PACKED` varchar(10) DEFAULT NULL,
23  `NULLABLE` varchar(3) NOT NULL DEFAULT '',
24  `INDEX_TYPE` varchar(16) NOT NULL DEFAULT '',
25  `COMMENT` varchar(16) DEFAULT NULL,
26  `INDEX_COMMENT` varchar(1024) NOT NULL DEFAULT ''
27) ENGINE=MEMORY DEFAULT CHARSET=utf8
281 row in set (0.00 sec)
29
30mysql> 
31     
32CREATE INDEX index_name ON mytable(column); 
33     
34SELECT COUNT(1) IndexIsThere FROM INFORMATION_SCHEMA.STATISTICS
35WHERE table_schema=DATABASE() AND table_name='mytable' AND index_name='index_name'; 
36     
37DELIMITER $$
38
39DROP PROCEDURE IF EXISTS `adam_matan`.`CreateIndex` $$
40CREATE PROCEDURE `adam_matan`.`CreateIndex`
41(
42    given_database VARCHAR(64),
43    given_table    VARCHAR(64),
44    given_index    VARCHAR(64),
45    given_columns  VARCHAR(64)
46)
47BEGIN
48
49    DECLARE IndexIsThere INTEGER;
50
51    SELECT COUNT(1) INTO IndexIsThere
52    FROM INFORMATION_SCHEMA.STATISTICS
53    WHERE table_schema = given_database
54    AND   table_name   = given_table
55    AND   index_name   = given_index;
56
57    IF IndexIsThere = 0 THEN
58        SET @sqlstmt = CONCAT('CREATE INDEX ',given_index,' ON ',
59        given_database,'.',given_table,' (',given_columns,')');
60        PREPARE st FROM @sqlstmt;
61        EXECUTE st;
62        DEALLOCATE PREPARE st;
63    ELSE
64        SELECT CONCAT('Index ',given_index,' already exists on Table ',
65        given_database,'.',given_table) CreateindexErrorMessage;   
66    END IF;
67
68END $$
69
70DELIMITER ; 
71     
72mysql> show create table pixelsG
73*************************** 1. row ***************************
74       Table: pixels
75Create Table: CREATE TABLE `pixels` (
76  `id` int(11) NOT NULL AUTO_INCREMENT,
77  `type` varchar(30) DEFAULT NULL,
78  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
79  `pixel_data` blob,
80  PRIMARY KEY (`id`)
81) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=latin1
821 row in set (0.00 sec)
83
84mysql> call createindex('adam_matan','pixels','type_timestamp_id_ndx','type,timestamp,id');
85Query OK, 0 rows affected (0.20 sec)
86
87mysql> show create table pixelsG
88*************************** 1. row ***************************
89       Table: pixels
90Create Table: CREATE TABLE `pixels` (
91  `id` int(11) NOT NULL AUTO_INCREMENT,
92  `type` varchar(30) DEFAULT NULL,
93  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
94  `pixel_data` blob,
95  PRIMARY KEY (`id`),
96  KEY `type_timestamp_id_ndx` (`type`,`timestamp`,`id`)
97) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=latin1
981 row in set (0.00 sec)
99
100mysql> call createindex('adam_matan','pixels','type_timestamp_id_ndx','type,timestamp,id');
101+-----------------------------------------------------------------------+
102| CreateindexErrorMessage                                               |
103+-----------------------------------------------------------------------+
104| Index type_timestamp_id_ndx Already Exists on Table adam_matan.pixels |
105+-----------------------------------------------------------------------+
1061 row in set (0.00 sec)
107
108Query OK, 0 rows affected (0.01 sec)
109
110mysql> 
111     
112select if (
113    exists(
114        select distinct index_name from information_schema.statistics 
115        where table_schema = 'schema_db_name' 
116        and table_name = 'tab_name' and index_name like 'index_1'
117    )
118    ,'select ''index index_1 exists'' _______;'
119    ,'create index index_1 on tab_name(column_name_names)') into @a;
120PREPARE stmt1 FROM @a;
121EXECUTE stmt1;
122DEALLOCATE PREPARE stmt1; 
123     
124SELECT COUNT(*)
125FROM information_schema.statistics
126WHERE TABLE_SCHEMA = DATABASE()
127  AND TABLE_NAME = 'table_name' 
128  AND INDEX_NAME = 'index_name'; 
129     
130ALTER TABLE `my_table` ADD INDEX `col_idx` (`col` DESC); 
131     
132try {
133    $db->query('ALTER TABLE `my_table` ADD INDEX `col_idx` (`col` DESC) VISIBLE;');
134} catch (PDOException $ex) {
135    // Index already exists, do nothing
136}