本文共 6813 字,大约阅读时间需要 22 分钟。
1.开启事务:
行级锁
session1:
#begin; #select *from pub_sev_db.t_push_log where FmsgId = 3 for update;//注意调节语句必须为主键才能执行 行锁定 #commit
session2:
可以执行select语句,但无法修改FmsgId = 3的语句:
比如执行:
update pub_sev_db.t_push_log set Fmodify_time=now() where FmsgId=3;
会在这里阻塞,
同理
begin; select *from pub_sev_db.t_push_log where FmsgId = 3 for update;//也会阻塞 commit
直到session1 commit事务或是事务被结束!!!
mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
2.添加UNIQUE(唯一索引)
3.添加INDEX(普通索引)
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
4.添加FULLTEXT(全文索引)
mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
5.添加多列索引
ALTER | Allows use of ALTER TABLE. |
ALTER ROUTINE | Alters or drops stored routines. |
CREATE | Allows use of CREATE TABLE. |
CREATE ROUTINE | Creates stored routines. |
CREATE TEMPORARY TABLE | Allows use of CREATE TEMPORARY TABLE. |
CREATE USER | Allows use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES. |
CREATE VIEW | Allows use of CREATE VIEW. |
DELETE | Allows use of DELETE. |
DROP | Allows use of DROP TABLE. |
EXECUTE | Allows the user to run stored routines. |
FILE | Allows use of SELECT... INTO OUTFILE and LOAD DATA INFILE. |
INDEX | Allows use of CREATE INDEX and DROP INDEX. |
INSERT | Allows use of INSERT. |
LOCK TABLES | Allows use of LOCK TABLES on tables for which the user also has SELECT privileges. |
PROCESS | Allows use of SHOW FULL PROCESSLIST. |
RELOAD | Allows use of FLUSH. |
REPLICATION | Allows the user to ask where slave or master |
CLIENT | servers are. |
REPLICATION SLAVE | Needed for replication slaves. |
SELECT | Allows use of SELECT. |
SHOW DATABASES | Allows use of SHOW DATABASES. |
SHOW VIEW | Allows use of SHOW CREATE VIEW. |
SHUTDOWN | Allows use of mysqladmin shutdown. |
SUPER | Allows use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL SQL statements. Allows mysqladmin debug command. Allows one extra connection to be made if maximum connections are reached. |
UPDATE | Allows use of UPDATE. |
USAGE | Allows connection without any specific privileges. |
#############################################################
常用语句
#SELECT NOW() #SELECT unix_timestamp(now()) from sys.sys_config limit 1 #create database pub_sev_db; use pub_sev_db; /* CREATE TABLE `pub_sev_db`.`t_access_token` ( `Fappid` VARCHAR(64) NOT NULL, `Fsecret` VARCHAR(64) NOT NULL, `Fappid_Type` TINYINT(2) NULL, `Faccess_token_refresh` TINYINT(2) NULL, `Fjsapi_ticket_refresh` TINYINT(2) NULL, `Faccess_token` VARCHAR(1024) NULL, `Facctok_expires` INT(11) NULL, `Facctok_invalid_stamp` INT(11) NULL, `Fjsapi_ticket` VARCHAR(1024) NULL, `Fticket_expires` INT(11) NULL, `Fticket_invalid_stamp` INT(11) NULL, `Fcreate_time` DATETIME NULL, `Fmodify_time` DATETIME NULL, `Freserved_1` INT(11) NULL, `Freserved_2` INT(11) NULL, `Freserved_3` VARCHAR(128) NULL, `Freserved_4` VARCHAR(128) NULL, `Freserved_5` DATETIME NULL, `Freserved_6` DATETIME NULL, PRIMARY KEY (`Fappid`)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; */ #明文 insert into pub_sev_db.t_access_token(Fappid,Fsecret,Fappid_Type,Faccess_token_refresh,Fjsapi_ticket_refresh,Fcreate_time,Fmodify_time) values ('wxb373aef795a268','05d7159b5430690f9c938b3',2,1,1,now(),now()); #加密 insert into pub_sev_db.t_access_token(Fappid,Fsecret,Fappid_Type,Faccess_token_refresh,Fjsapi_ticket_refresh,Fcreate_time,Fmodify_time) values ('wxb373aefa21868','B095K72CsZPTYFr0EEt0kde+jArPfINkn8BTh3Y8gjV1l+ztPkOM5Q==',2,1,1,now(),now()); #update `pub_sev_db`.`t_access_token` set Fmodify_time= now() where Fappid= 'wxb373aef795a21868' ; #select * from `pub_sev_db`.`t_access_token`; #修改字段属性 ALTER TABLE `pub_sev_db`.`t_access_token` CHANGE COLUMN `Fsecret` `Fsecret` VARCHAR(72) NOT NULL ; #删除一条记录 delete from pub_sev_db.t_access_token where Fappid = 'wxb373aef795a21868'; #修改表名 ALTER TABLE `pub_sev_db`.`t_push_log` RENAME TO `pub_sev_db`.`t_push_log_1` ; select now(); CREATE TABLE `pub_sev_db`.`t_push_log` ( `FmsgId` INT(11) NOT NULL AUTO_INCREMENT, `Fappid` VARCHAR(64) NULL, `Fopenid` VARCHAR(72) NULL, `FpushMsgId` VARCHAR(72) NULL, `FpushResult` VARCHAR(32) NULL, `FpushResultDec` VARCHAR(128) NULL, `FpushStatus` TINYINT(5) NULL, `FpushCnt` TINYINT(5) NULL, `Fmsg` VARCHAR(3096) NULL, `Fcreate_time` DATETIME NULL, `Fmodify_time` DATETIME NULL, `Freserved_1` INT(11) NULL, `Freserved_2` INT(11) NULL, `Freserved_3` VARCHAR(128) NULL, `Freserved_4` VARCHAR(128) NULL, `Freserved_5` DATETIME NULL, `Freserved_6` DATETIME NULL, PRIMARY KEY (`FmsgId`)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; #insert into pub_sev_db.t_push_log(Fappid,Fopenid,FpushResult,FpushResultDec,FpushStatus,FpushCnt,Fmsg,Fcreate_time,Fmodify_time) values('wxb373aef795a21868','121334fdgsrqag','pushing','null',1,0,'msgvalue:hello',now(),now()); #返回为当前会话的所有作用域中的任何表最后生成的标识值 # SELECT @@Identity AS FmsgId; #SELECT * FROM pub_sev_db.t_push_log; #insert into pub_sev_db.t_push_log(Fappid,Fopenid,FpushResult,FpushResultDec,FpushStatus,FpushCnt,Fmsg,Fcreate_time,Fmodify_time) values('wxb373aef795a21868','121334fdgsrqag','pushing','null',1,0,'msgvalue:hello',now(),now()); #返回为当前会话的所有作用域中的任何表最后生成的标识值 #SELECT distinct @@Identity AS FmsgId from pub_sev_db.t_push_log;
转载地址:http://nnrgi.baihongyu.com/