Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE INDEX IF NOT EXISTS index_name ON table(column)
- ERROR 1064 (42000): You have an error in your SQL syntax;...
- ALTER TABLE table_name ADD INDEX (column_to_index);
- ALTER TABLE table_name ADD INDEX (column_to_index);
- mysql> show create table statisticsG
- *************************** 1. row ***************************
- Table: STATISTICS
- Create Table: CREATE TEMPORARY TABLE `STATISTICS` (
- `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
- `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
- `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
- `NON_UNIQUE` bigint(1) NOT NULL DEFAULT '0',
- `INDEX_SCHEMA` varchar(64) NOT NULL DEFAULT '',
- `INDEX_NAME` varchar(64) NOT NULL DEFAULT '',
- `SEQ_IN_INDEX` bigint(2) NOT NULL DEFAULT '0',
- `COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
- `COLLATION` varchar(1) DEFAULT NULL,
- `CARDINALITY` bigint(21) DEFAULT NULL,
- `SUB_PART` bigint(3) DEFAULT NULL,
- `PACKED` varchar(10) DEFAULT NULL,
- `NULLABLE` varchar(3) NOT NULL DEFAULT '',
- `INDEX_TYPE` varchar(16) NOT NULL DEFAULT '',
- `COMMENT` varchar(16) DEFAULT NULL,
- `INDEX_COMMENT` varchar(1024) NOT NULL DEFAULT ''
- ) ENGINE=MEMORY DEFAULT CHARSET=utf8
- 1 row in set (0.00 sec)
- mysql>
- CREATE INDEX index_name ON mytable(column);
- SELECT COUNT(1) IndexIsThere FROM INFORMATION_SCHEMA.STATISTICS
- WHERE table_schema=DATABASE() AND table_name='mytable' AND index_name='index_name';
- DELIMITER $$
- DROP PROCEDURE IF EXISTS `adam_matan`.`CreateIndex` $$
- CREATE PROCEDURE `adam_matan`.`CreateIndex`
- (
- given_database VARCHAR(64),
- given_table VARCHAR(64),
- given_index VARCHAR(64),
- given_columns VARCHAR(64)
- )
- BEGIN
- DECLARE IndexIsThere INTEGER;
- SELECT COUNT(1) INTO IndexIsThere
- FROM INFORMATION_SCHEMA.STATISTICS
- WHERE table_schema = given_database
- AND table_name = given_table
- AND index_name = given_index;
- IF IndexIsThere = 0 THEN
- SET @sqlstmt = CONCAT('CREATE INDEX ',given_index,' ON ',
- given_database,'.',given_table,' (',given_columns,')');
- PREPARE st FROM @sqlstmt;
- EXECUTE st;
- DEALLOCATE PREPARE st;
- ELSE
- SELECT CONCAT('Index ',given_index,' already exists on Table ',
- given_database,'.',given_table) CreateindexErrorMessage;
- END IF;
- END $$
- DELIMITER ;
- mysql> show create table pixelsG
- *************************** 1. row ***************************
- Table: pixels
- Create Table: CREATE TABLE `pixels` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `type` varchar(30) DEFAULT NULL,
- `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
- `pixel_data` blob,
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=latin1
- 1 row in set (0.00 sec)
- mysql> call createindex('adam_matan','pixels','type_timestamp_id_ndx','type,timestamp,id');
- Query OK, 0 rows affected (0.20 sec)
- mysql> show create table pixelsG
- *************************** 1. row ***************************
- Table: pixels
- Create Table: CREATE TABLE `pixels` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `type` varchar(30) DEFAULT NULL,
- `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
- `pixel_data` blob,
- PRIMARY KEY (`id`),
- KEY `type_timestamp_id_ndx` (`type`,`timestamp`,`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=latin1
- 1 row in set (0.00 sec)
- mysql> call createindex('adam_matan','pixels','type_timestamp_id_ndx','type,timestamp,id');
- +-----------------------------------------------------------------------+
- | CreateindexErrorMessage |
- +-----------------------------------------------------------------------+
- | Index type_timestamp_id_ndx Already Exists on Table adam_matan.pixels |
- +-----------------------------------------------------------------------+
- 1 row in set (0.00 sec)
- Query OK, 0 rows affected (0.01 sec)
- mysql>
- select if (
- exists(
- select distinct index_name from information_schema.statistics
- where table_schema = 'schema_db_name'
- and table_name = 'tab_name' and index_name like 'index_1'
- )
- ,'select ''index index_1 exists'' _______;'
- ,'create index index_1 on tab_name(column_name_names)') into @a;
- PREPARE stmt1 FROM @a;
- EXECUTE stmt1;
- DEALLOCATE PREPARE stmt1;
- SELECT COUNT(*)
- FROM information_schema.statistics
- WHERE TABLE_SCHEMA = DATABASE()
- AND TABLE_NAME = 'table_name'
- AND INDEX_NAME = 'index_name';
- ALTER TABLE `my_table` ADD INDEX `col_idx` (`col` DESC);
- try {
- $db->query('ALTER TABLE `my_table` ADD INDEX `col_idx` (`col` DESC) VISIBLE;');
- } catch (PDOException $ex) {
- // Index already exists, do nothing
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement