SHARE
TWEET

Untitled

a guest Jul 17th, 2019 51 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE INDEX IF NOT EXISTS index_name ON table(column)
  2. ERROR 1064 (42000): You have an error in your SQL syntax;...
  3.      
  4. ALTER TABLE table_name ADD INDEX (column_to_index);
  5. ALTER TABLE table_name ADD INDEX (column_to_index);
  6.      
  7. mysql> show create table statisticsG
  8. *************************** 1. row ***************************
  9.        Table: STATISTICS
  10. Create 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
  28. 1 row in set (0.00 sec)
  29.  
  30. mysql>
  31.      
  32. CREATE INDEX index_name ON mytable(column);
  33.      
  34. SELECT COUNT(1) IndexIsThere FROM INFORMATION_SCHEMA.STATISTICS
  35. WHERE table_schema=DATABASE() AND table_name='mytable' AND index_name='index_name';
  36.      
  37. DELIMITER $$
  38.  
  39. DROP PROCEDURE IF EXISTS `adam_matan`.`CreateIndex` $$
  40. CREATE 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. )
  47. BEGIN
  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.  
  68. END $$
  69.  
  70. DELIMITER ;
  71.      
  72. mysql> show create table pixelsG
  73. *************************** 1. row ***************************
  74.        Table: pixels
  75. Create 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
  82. 1 row in set (0.00 sec)
  83.  
  84. mysql> call createindex('adam_matan','pixels','type_timestamp_id_ndx','type,timestamp,id');
  85. Query OK, 0 rows affected (0.20 sec)
  86.  
  87. mysql> show create table pixelsG
  88. *************************** 1. row ***************************
  89.        Table: pixels
  90. Create 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
  98. 1 row in set (0.00 sec)
  99.  
  100. mysql> 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. +-----------------------------------------------------------------------+
  106. 1 row in set (0.00 sec)
  107.  
  108. Query OK, 0 rows affected (0.01 sec)
  109.  
  110. mysql>
  111.      
  112. select 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;
  120. PREPARE stmt1 FROM @a;
  121. EXECUTE stmt1;
  122. DEALLOCATE PREPARE stmt1;
  123.      
  124. SELECT COUNT(*)
  125. FROM information_schema.statistics
  126. WHERE TABLE_SCHEMA = DATABASE()
  127.   AND TABLE_NAME = 'table_name'
  128.   AND INDEX_NAME = 'index_name';
  129.      
  130. ALTER TABLE `my_table` ADD INDEX `col_idx` (`col` DESC);
  131.      
  132. try {
  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. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top