- MYSQL中只有INNODB和BDB类型的数据表才能支持事务处理!其他的类型是不支持!
SELECT @@global.tx_isolation; //查看系统当前隔离级别
SELECT @@session.tx_isolation; //session级别的事务隔离级别
SELECT @@tx_isolation; //查看当前会话隔离级别
- 需注意的是若指定了GLOBAL关键字则会对所有后续的session生效而对当前session无效。若指定了SESSION关键字则会对当前session中的后续的事务生效而对当前事务无效。若没有指定任何关键字则仅对当前session中接下来的一个事务生效。
设置当前会话隔离级别
set session transaction isolatin level repeatable read;
设置系统当前隔离级别
set global transaction isolation level repeatable read;
用户可以用SET TRANSACTION语句改变单个会话或者所有新进连接的隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
默认的行为(不带session和global)是为下一个(未开始)事务设置隔离级别。如果你使用GLOBAL关键字,语句在全局对从那点开始创建的所有新连接(除了不存在的连接)设置默认事务级别。你需要SUPER权限来做这个。使用SESSION 关键字为将来在当前连接上执行的事务设置默认事务级别。 任何客户端都能自由改变会话隔离级别(甚至在事务的中间),或者为下一个事务设置隔离级别。网上有人使用set tx_isolation命令:
mysql> set tx_isolation=’read-committed’;
事务隔离变了。
网上还有人这样写 set @@tx_isolation命令,但这个命令是有问题的。
set @@tx_isolation=’read-committed’;
session事物的隔离级别并没有改变。新建存储过程,输入如下代码,这即时一个事务的例子
BEGIN
DECLARE t_error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
START TRANSACTION;
INSERT INTO TExchangeInfo2(FExchangeNo) VALUES('2DCE');
INSERT INTO TExchangeInfo2(FExchangeNo) VaLUES('2ZCE');
INSERT INTO TExchangeInfo2(FExchangeNo) VaLUES('2ZCE');
IF t_error = 1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
select t_error;
END
- 当事务用如下方式时,删除数据经常性卡死,暂时不知道原因,测试上次事务并未提交导致
BEGIN
DECLARE t_error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
START TRANSACTION;
INSERT INTO TExchangeInfo2(FExchangeNo) VALUES('2DCE');
INSERT INTO TExchangeInfo2(FExchangeNo) VaLUES('2ZCE');
SAVEPOINT save1;
INSERT INTO TExchangeInfo2(FExchangeNo) VaLUES('2ZCE');
IF t_error = 1 THEN
ROLLBACK TO save1;
ELSE
COMMIT;
END IF;
select t_error;
END
- 游标
BEGIN
DECLARE CurDone1 INT DEFAULT 0;
DECLARE ttt VARCHAR(20);
DECLARE PositionCursor CURSOR FOR SELECT FExchangeNo from TExchangeInfo;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET CurDone1 = 1;
OPEN PositionCursor;
FETCH PositionCursor INTO ttt;
while not CurDone1 DO
SELECT ttt;
SET CurDone1 = 0;
FETCH PositionCursor INTO ttt;
END WHILE;
END
BEGIN
DECLARE CurDone1 INT DEFAULT 0;
DECLARE ttt VARCHAR(20);
DECLARE PositionCursor CURSOR FOR SELECT FExchangeNo from TExchangeInfo ORDER BY FExchangeNo;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET CurDone1 = 1;
OPEN PositionCursor;
FETCH PositionCursor INTO ttt;
while not CurDone1 DO
IF ttt = '11DCE' THEN
update TExchangeInfo SET FExchangeNo = CONCAT('1',ttt) WHERE FExchangeNo = ttt;
END IF;
SELECT ttt;
SET CurDone1 = 0;
FETCH PositionCursor INTO ttt;
END WHILE;
END
- mysql 不支持Current of cursor_name这种用法
update TExchangeInfo SET FExchangeNo = CONCAT(‘1’,ttt) WHERE CURRENT OF PositionCursor;
MySQL的游标是向前只读的,也就是说,你只能顺序地从开始往后读取结果集,不能从后往前,也不能直接跳到中间的记录.
-- COLLATE 指定字符排序方式
-- UNIQUE 约束唯一标识数据库表中的记录,UNIQUE和PRIMARY KEY 约束列或列集合提供了唯一性的保证
-- PRIMARY KEY拥有自定义的UNIQUE约束,每个表可以有多个UNIQUE约束,但是每个表只能有一个PRIMARY KEY约束
CREATE TABLE `TExchangeInfo`
(
`FSerialID` INT(10) NOT NULL AUTO_INCREMENT,
`FExchangeNo` VARCHAR(10) COLLATE utf8_bin NOT NULL DEFAULT '',
`FExchangeName` VARCHAR(20) COLLATE utf8_bin NOT NULL DEFAULT '',
`FExchangeReportNo` VARCHAR(20) COLLATE utf8_bin NOT NULL DEFAULT '',
`FOperatorNo` VARCHAR(20) COLLATE utf8_bin NOT NULL DEFAULT '',
`FExchangeDescribe` VARCHAR(50) COLLATE utf8_bin DEFAULT '',
`FOperatorTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`FExchangeSequenceNo` INT(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`FExchangeNo`),
UNIQUE KEY `FSerialID` (`FSerialID`)
)ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
DROP PROCEDURE IF EXISTS `PM_Add_ExchangeInfo`;
CREATE DEFINER = `root`@`%` PROCEDURE `PM_Add_ExchangeInfo`(ExchangeNo varchar(10),ExchangeName varchar(20),ExchangeReportNo varchar(20),OperatorNo varchar(20),ExchangeDescribe varchar(50),OperatorTime timestamp,ExchangeSequenceNo int(11))
BEGIN
DECLARE OperatorLogInfo VARCHAR(5000);
DECLARE OperateTime TIMESTAMP;
SET OperateTime = NOW();
INSERT INTO FExchangeInfo(FSerialID,FExchangeNo,FExchangeName,FExchangeReportNo,FOperatorNo,FExchangeDescribe,FOperatorTime,FExchangeSequenceNo)
VALUES(ExchangeNo,ExchangeName,ExchangeReportNo,OperatorNo,ExchangeDescribe,OperatorTime,ExchangeSequenceNo);
END;
- 创建表时只能指定一个主键,当需要使用多个字段作为联合主键时,可以使用这种方式
CREATE TABLE ttt
(
FID INT,
FName VARCHAR(20),
FEnglishName VARCHAR(20),
PRIMARY KEY(FID, FName)
);
如果这样写会报错
CREATE TABLE ttt
(
FID INT PRIMARY KEY,
FName VARCHAR(20) PRIMARY KEY,
FEnglishName VARCHAR(20)
);
如果在创建表时候没有指定主键,也可以在创建完成之后,对标进行修改,其增加主键的格式如下
CREATE TABLE ttt
(
FID INT,
FName VARCHAR(20),
FEnglishName VARCHAR(20)
);
ALTER TABLE ttt ADD PRIMARY KEY(FID, FName);
- 返回自增流号的方法 select LAST_INSERT_ID