Mysql - 增刪改

因為項目原因, mysql用了兩年了, 但是一直都未曾去總結過. 最近也是領導讓總結項目, 才想起把mysql的使用小結一下.

一、 Create

1. 單條插入, sql格式: insert into (列名) values(列值);

INSERT INTO test.tch_teacher (  Sex, BId, NO, NAME, IsDoublePosition, CreateDate )
VALUES (  1, '123123123', '123123123', 'Insert', 0, NOW() );

對於自增的 Id, 是不需要寫的, 資料庫會自動生成, 但是如果一不小心寫上去了, 只要你的Id值, 在資料庫中不存在, 是可以插入進去的.

在mysql中, 就算你插入的 id 為負數, 也是可以插入成功的. 如果資料庫中已存在你想插入的 id 值, 則會直接報錯.

2. 多條插入, sql格式: insert into (列名) values(列值),(列值),(列值);

INSERT INTO test.tch_teacher (  Sex, BId, NO, NAME, IsDoublePosition, CreateDate ) 
VALUES 
( 2, '123123123', '123123123', 'Insert', 0, NOW() ),
( 3, '123123123', '123123123', 'Insert', 0, NOW() ),
( 4, '123123123', '123123123', 'Insert', 0, NOW() );

新增多條的時候, 也可以迴圈調用單條插入語句去插入, 不過, 這種方式並不推薦使用, 因為, 這種方式, 消費更多性能和時間.

3. 表插入

可以新建一張臨時表: tch_teacher_temp

CREATE TABLE `tch_teacher_temp` (
  `Sex` smallint(6) DEFAULT NULL,
  `BId` varchar(36) CHARACTER SET utf8 DEFAULT NULL,
  `No` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  `Name` varchar(30) CHARACTER SET utf8 DEFAULT NULL,
  `IsDoublePosition` bit(1) DEFAULT NULL,
  `CreateDate` datetime DEFAULT NULL,
  PRIMARY KEY (`Id`)
) 

這裡有一個取巧的方式, 來獲取建表sql

show create table tch_teacher;

然後修改一下表名,刪除主鍵(也可不刪)就可以了.

insert into tch_teacher (Sex, BId, NO, NAME, IsDoublePosition, CreateDate) 
select * from tch_teacher_temp;
-- 或者
insert into tch_teacher (Sex, BId, NO, NAME, IsDoublePosition, CreateDate) 
select Sex, BId, NO, NAME, IsDoublePosition, CreateDate from tch_teacher_temp;

4. 性能比較

我粗略測試了一下, 十萬級和百萬級的數據量, 插入1000條數據. tch_teacher表, 我建了三個索引:Sex, BId, IsDoublePosition

1). 不使用事務, 一條一條插入, 迴圈以下這條語句, 1000次

for (int i = 0; i < 1000; i++)
{
    var param = new { BId = Guid.NewGuid(), Name = names[ran.Next(9)] + names[ran.Next(9)] + i, IsDoublePosition = i % 2, CreateDate = DateTime.Now, Sex = i % 2, No = ran.Next(100000, 9999999) };
    conn.Execute(insertSql, param);
}

2). 在使用事務的情況下,  還是迴圈這條語句, 不同的是, 在迴圈結束處, 加入事務提交(這次的插入是在上次的數據量基礎上, 也就是說, 這次插入前, 數據比上次多1000條)

var tran = conn.BeginTransaction();
for (int i = 0; i < 1000; i++)
{
    var param = new { BId = Guid.NewGuid(), Name = names[ran.Next(9)] + names[ran.Next(9)] + i, IsDoublePosition = i % 2, CreateDate = DateTime.Now, Sex = i % 2, No = ran.Next(100000, 9999999) };
    conn.Execute(insertSql, param, tran);
}
tran.Commit();

3). 拼接sql語句的情況下, 由於是"(),(),();"格式的, 所以參數我全做入sql中了, 這裡參數化的方式, 不好做. (這次的插入也是在上次的數據量基礎上, 也就是說, 這次插入前, 數據比上次多1000條)

StringBuilder sb = new StringBuilder("insert into tch_teacher(BId,Sex,No, Name, IsDoublePosition, CreateDate) values ", 10000);
for (int i = 0; i < 1000; i++)
{
    sb.Append(string.Format("('{0}', {1}, '{2}', '{3}', {4}, '{5}'),",
        Guid.NewGuid(),
            i % 2,
            ran.Next(100000, 9999999),
        names[ran.Next(9)] + names[ran.Next(9)] + i,
        i % 2,
        DateTime.Now.ToString("yyyy-MM-dd")));
}
sb.Remove(sb.Length - 1, 1);
conn.Execute(sb.ToString());

這種方式, 有兩個不好的地方, 一個是不能參數化, 另一個是如果插入數據較多, 會導致sql語句太長, 所以並不推薦

4). 建臨時表的方式, 這裡我是事先吧臨時表建好的, 在代碼裡面就沒有建了

var insertSql = @"insert into tch_teacher_temp(BId,Sex,No, Name, IsDoublePosition, CreateDate) values(@BId, @Sex, @No, @Name, @IsDoublePosition, @CreateDate);";
var tran = conn.BeginTransaction();
for (int i = 0; i < 1000; i++)
{
    var param = new { BId = Guid.NewGuid(), Name = names[ran.Next(9)] + names[ran.Next(9)] + i, IsDoublePosition = i % 2, CreateDate = DateTime.Now, Sex = i % 2, No = ran.Next(100000, 9999999) };
    conn.Execute(insertSql, param, tran);
}
conn.Execute(@"insert into tch_teacher (Sex, BId, NO, NAME, IsDoublePosition, CreateDate) select Sex, BId, NO, NAME, IsDoublePosition, CreateDate from tch_teacher_temp;", null, tran1);
tran.Commit();

這種方式, 每次都要新建表, 刪除表. 也是挺麻煩的, 這裡的測試, 就沒有包含新建表和刪除表了

結果:

                     十萬(ms)              百萬(ms)

sql拼接           230                      359

事務提交         412                      511

臨時表            661                     1424

一條一條       27606                   36620

除了一條一條提交方式, 其他的在時間上, 還是能接受的, 但是推薦使用第2種, 事務提交方式, 易用, 清晰, 省事.

二、Delete

刪除就相對簡單多了. 刪除sql的格式: delete from 表名 where 條件

刪除的時候, 如果不加where條件, 就是刪除整張表的數據, 相當於 where 1=1 ;

delete from tch_teacher where id=1;

這是一條最簡單的語句了.

刪除的時候, 對於主鍵的自增沒有影響. 比如主鍵為 1,2,3

這時候刪除了3, 再插入一條數據, 主鍵為從4開始.

如果想要讓主鍵又從1開始的話, 需要使用truncate

truncate table tch_teacher ;

這樣, 表回歸初始狀態.

有時候, 通過where查找後, 能得出很多條數據, 但是我只想刪除其中的前幾條, 那怎麼辦呢. 有辦法

delete from tch_teacher where isDoublePosition=1 order by id limit 6;

這條語句, 就是刪除 滿足條件的, 前6條數據

三、Update

sql格式: update 表名  set 列=值 where 條件

update的where條件也是可以不加的, 不加的情況下, 修改的就是全部數據.

update tch_teacher set name='黑茶' where id=3;

修改的時候, 也是可以通過連表的方式, 去修改數據的.

update  tch_teacher , tch_contact set tch_teacher.`Name`='紅茶' where tch_teacher.Id=tch_contact.TId and tch_contact.Id=1003;