MySql SqlServer Sqlite中关于索引的创建

最近要更新Cocon90.Db库，令其ORM创建表时实现索引的添加。因此总结下列常用Sql，供大家学习与参考。

一、SqlServer中创建索引可以这样：

if not exists (select * from sysobjects where id = object_id('{0}') and OBJECTPROPERTY(id, 'IsUserTable') = 1) Create Table Test (
  RowId VARCHAR(36) NOT NULL,
  Name VARCHAR(255) NOT NULL ,
  Type int NULL,
  PRIMARY KEY (RowId,Name),
  INDEX idxType(Type),
  INDEX idxName(Name)
);

二、MySql中需要这样：

CREATE TABLE IF NOT EXISTS Test (
  RowId VARCHAR(36) NOT NULL,
  Name VARCHAR(255) NOT NULL ,
  Type int NULL,
  PRIMARY KEY (RowId,Name),
  INDEX idxType(Type),
  INDEX idxName(Name)
);

三、Sqlite中需要这样：

CREATE TABLE IF NOT EXISTS Test (
  RowId VARCHAR(36) NOT NULL,
  Name VARCHAR(255) NOT NULL ,
  Type int NULL,
  PRIMARY KEY (RowId,Name)
);
create index IF NOT EXISTS idx3 on Test(Type);
create index IF NOT EXISTS idx2 on Test(Name);

 

四、SqlServer中有则删除索引，无则创建索引的常用语句有：

Create Index idxType on Test (Type,Name)
with (drop_existing = on);

或者

IF Not EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('Test') AND NAME ='idxType1')
CREATE INDEX [idxType1] ON Test(Type,Name);

 

五、Sqlite中无则创建索引的常用语句有：

create index IF NOT EXISTS idx3 on Test(Type);
create index IF NOT EXISTS idx2 on Test(Name);

 

六、MySql中对于无则创建索引的常用语句有：

DROP PROCEDURE IF EXISTS schema_change;
CREATE PROCEDURE schema_change() BEGIN
  IF NOT EXISTS (SELECT * FROM information_schema.statistics WHERE table_schema=database() AND table_name = 'Test' AND index_name = 'idxType')
  THEN   ALTER TABLE `Test` ADD INDEX `idxType` ( `Type` ); END IF;
END;  CALL schema_change(); DROP PROCEDURE IF EXISTS schema_change;

 

转载于:https://www.cnblogs.com/songxingzhu/p/6813922.html