Mysql-Modify/delete

Tag: On () bit through the. com upd database cause test Impact

Because of the project, MySQL has been used for two years, but has never been summed up. Recently also lead to summarize the project, just think of MySQL use summary.

First, Create

1. Single INSERT, SQL format: INSERT into (column name) VALUES (column value);

INSERT  into Test.tch_teacher (  Sex, BId, NO, NAME, Isdoubleposition, createdate)VALUES (  1  '123123123'123123123'Insert ' 0, now ());

For the self-increment of the Id, is not required to write, the database will be generated automatically, but if it is accidentally written up, as long as your ID value, in the database does not exist, can be inserted into the.

In MySQL, even if you insert an ID that is negative, you can insert it successfully. If the ID value you want to insert already exists in the database, the error will be directly.

2. Multiple inserts, SQL format: INSERT into (column name) VALUES (column value), (column value), (column value);

INSERT  intoTest.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 ());

When adding multiple bars, it is also possible to iterate through a single INSERT statement, but this is not recommended because, in this way, it consumes more performance and time.

3. Table Insert

You can create a new temporary table: tch_teacher_temp

CREATE TABLE' Tch_teacher_temp ' (' Sex 'smallint(6)DEFAULT NULL, ' BId 'varchar( $)CHARACTER SETUtf8DEFAULT NULL, ' No 'varchar( -)CHARACTER SETUtf8DEFAULT NULL, ' Name 'varchar( -)CHARACTER SETUtf8DEFAULT NULL, ' isdoubleposition 'bit(1)DEFAULT NULL, ' CreateDate 'datetime DEFAULT NULL,  PRIMARY KEY(' Id '))

Here's a trickery way to get the SQL to build the table

Create table Tch_teacher;

Then modify the table name, delete the primary key (also can not delete) on it.

Insert  into  Select* from tch_teacher_temp;--or insert into   Select from Tch_teacher_temp;

4. Performance Comparison

I had a rough test of the amount of data at level 100,000 and millions, inserting 1000 data. Tch_teacher table, I built three indexes: Sex, BId, isdoubleposition

1). Do not use transactions, a single insert, loop the following statement, 1000 times

 for (int0, i++) {    varnew {BId = Guid.NewGuid (), Name = names [Ran. Next (9)] + Names[ran. Next (922, No = ran. Next (1000009999999)};    Conn. Execute (Insertsql, param);}

2). In the case of using a transaction, or loop the statement, the difference is that at the end of the loop, add a transaction commit (this time the insertion is based on the last data volume, that is, before the insertion, the data more than the last 1000)

var tran = Conn. BeginTransaction ();  for (int0, i++) {    varnew {BId = Guid.NewGuid (), Name = names [Ran. Next (9)] + Names[ran. Next (922, No = ran. Next (1000009999999)};    Conn. Execute (Insertsql, Param, Tran);} Tran.commit ();

3). Stitching the SQL statement in case, because it is "(), (), ();" format, so parameters I have all done into SQL, here parameterized way, not good to do. (This time the insertion is also based on the last data volume, that is, before this insertion, the data more than the last 1000)

StringBuilder SB =NewStringBuilder ("INSERT INTO Tch_teacher (Bid,sex,no, Name, isdoubleposition, createdate) values",10000); for(inti =0; I < +; 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 ());

This way, there are two bad places, one is not parameterized, the other is that if you insert more data, it will cause the SQL statement is too long, so it is not recommended

4). How to build a temporary table here, I'm going to do it beforehand. The temporary table was built, and it was not built in the code.

varInsertsql =@"INSERT INTO Tch_teacher_temp (Bid,sex,no, Name, isdoubleposition, CreateDate) VALUES (@BId, @Sex, @No, @Name, @ Isdoubleposition, @CreateDate);";varTran =Conn. BeginTransaction (); for(inti =0; I < +; i++){    varparam =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 ();

This way, each time you create a new table, delete the table. is also very troublesome, the test here, there is no new table and delete the table

Results:

100,000 (ms) million (MS)

SQL Stitching 230 359

Transaction Submission 412 511

Provisional table 661 1424

One strip 27606 36620

In addition to one submission method, the other in time, but also acceptable, but recommended the use of the 2nd, transaction submission method, easy-to-use, clear, convenient.

Second, Delete

It's a lot easier to delete. Delete SQL format: Delete from table name where condition

Delete the time, if not add where condition, is to delete the entire table of data, equivalent to where 1=1;

Delete  from where id=1;

This is the simplest statement.

When deleted, there is no effect on the self-increment of the primary key. For example, the primary key is

This time delete 3, then insert a data, the primary key is starting from 4.

If you want the primary key to start again from 1, you need to use truncate

truncate table Tch_teacher;

In this way, the table returns to its initial state.

Sometimes, there are a lot of pieces of data that can be found by where, but I just want to delete the first few of them. There is a way

Delete  from where isdoubleposition=1orderby6;

This statement is to delete the first 6 data that satisfies the condition

Third, Update

SQL format: Update table name set column = value where condition

The Where condition of the update can also be non-additive, without the addition of the case, the whole data is modified.

Update Set name=' black tea 'where id=3;

When modifying, it is also possible to modify the data by means of a table.

Update  set tch_teacher. ' Name '=' black tea 'where tch_teacher. Id= and Tch_contact. Id=1003;

Mysql-Modify/delete