【MYSQL索引】1 索引基础&数据准备部分

创建索引

在已经存在的表中创建索引可以使用ALTER TABLE语句或者CREATE INDEX语句。

1. 使用ALTER TABLE语句创建索引 ALTER TABLE语句创建索引的基本语法如下：

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]

2. 使用CREATE INDEX创建索引 CREATE INDEX语句可以在已经存在的表上添加索引，在MySQL中，
   CREATE INDEX被映射到一个ALTER TABLE语句上，基本语法结构为：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]

删除索引

1. 使用ALTER TABLE删除索引 ALTER TABLE删除索引的基本语法格式如下：

ALTER TABLE table_name DROP INDEX index_name;

2. 使用DROP INDEX语句删除索引 DROP INDEX删除索引的基本语法格式如下：

DROP INDEX index_name ON table_name;

mysql创建函数问题记录：

创建函数，假如报错：

This function has none of DETERMINISTIC…

由于开启过慢查询日志bin-log, 我们就必须为我们的function指定一个参数。
主从复制，主机会将写操作记录在bin-log日志中。从机读取bin-log日志，执行语句来同步数据。如果使用函数来操作数据，会导致从机和主键操作时间不一致。所以，默认情况下，mysql不开启创建函数设置。

查看mysql是否允许创建函数：

show variables like ‘log_bin_trust_function_creators’;

命令开启：允许创建函数设置：

set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。

mysqld重启，上述参数又会消失。永久方法：
windows下：my.ini[mysqld]加上：

log_bin_trust_function_creators=1

linux下：/etc/my.cnf下my.cnf[mysqld]加上：

log_bin_trust_function_creators=1

示例：

CREATE TABLE `student_info` (
	`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
	`student_id` INT NOT NULL,
	`name` VARCHAR ( 20 ) DEFAULT NULL,
	`course_id` INT NOT NULL,
	`class_id` INT ( 11 ) DEFAULT NULL,
	`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;
CREATE TABLE `course` (
	`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
	`course_id` INT NOT NULL,
	`course_name` VARCHAR ( 40 ) DEFAULT NULL,
	PRIMARY KEY ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

DELIMITER //
CREATE FUNCTION rand_string ( n INT ) RETURNS VARCHAR (255) #该函数会返回一个字符串
BEGIN
	DECLARE
		chars_str VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
	DECLARE
		return_str VARCHAR ( 255 ) DEFAULT '';
	DECLARE
		i INT DEFAULT 0;
	WHILE i < n DO
			SET return_str = CONCAT(return_str,SUBSTRING( chars_str, FLOOR(1+RAND()* 52), 1 ));
			SET i = i + 1;
		END WHILE;
	RETURN return_str;
END // 
DELIMITER;


#函数2：创建随机数函数
DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
RETURN i;
END //
DELIMITER ;

show variables like 'log_bin_trust_function_creators';

set global log_bin_trust_function_creators=1;




# 存储过程1：创建插入课程表存储过程
DELIMITER //
CREATE PROCEDURE insert_course( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO course (course_id, course_name ) VALUES
(rand_num(10000,10100),rand_string(6));
UNTIL i = max_num END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;

# 存储过程2：创建插入学生信息表存储过程
DELIMITER //
CREATE PROCEDURE insert_stu( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO student_info (course_id, class_id ,student_id ,NAME ) VALUES
(rand_num(10000,10100),rand_num(10000,10200),rand_num(1,200000),rand_string(6));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;


CALL insert_course(100); -- 0.038
CALL insert_stu(1000000); -- 341.03s