mysql 事务+索引

## 事务

CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop

CREATE TABLE `acount`(
	`id` INT(3) NOT NULL AUTO_INCREMENT ,
	`name` VARCHAR(30) NOT NULL,
	`money` DECIMAL (9,2) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8


INSERT INTO acount(`name`,`money`) 
VALUES('A',300.2),('B',400.33)



SET autocommit=0; -- 取消自动提交

START TRANSACTION -- 开启事务


UPDATE acount SET money=money-400 WHERE `name` ='A'
UPDATE acount SET money=money+400 WHERE `name`='B'


COMMIT  -- 提交
ROLLBACK;  -- 回滚

SET autocommit=1;  -- 返回

## 增加索引

CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '',
`email` VARCHAR(50) NOT NULL,
`phone` VARCHAR(20) DEFAULT '',
`gender` TINYINT(4) UNSIGNED DEFAULT '0',
`password` VARCHAR(100) NOT NULL DEFAULT '',
`age` TINYINT(4) DEFAULT NULL,
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8




DELIMITER $$   --定义函数必须要写
CREATE FUNCTION mock_data()
RETURNS INT   -- 注意是returns
DETERMINISTIC    -- 必须要写 mysql 8.0
BEGIN     
	DECLARE num INT  DEFAULT 1000000;
	DECLARE i INT  DEFAULT 0;
	WHILE i<num DO
		INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`) VALUES(CONCAT('用户',i),'1294582975@qq.com',CONCAT('18',FLOOR(RAND()*100000000)),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));	
		SET i=i+1;
	END WHILE;
	RETURN i;
	
END
SELECT mock_data()   -- 执行函数

-- 创建索引 create index 索引名 on 表（'字段'）

CREATE INDEX id_app_user_name ON app_user(`name`)   

-- 分析代码搜索了多少行
EXPLAIN SELECT * FROM app_user WHERE `name`='用户1'