Mysql基础

基础网站杂合

MySQL varchar最大长度，text类型占用空间剖析
Mysql使用存储过程插入十万条数据

基础SQL语句

//修改表字段属性
alter table doc_revisions modify PERM_GROUP_LIST varchar(2048) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '有權限的群組'

//修改索引 mysql 要先删除 再新增

DROP INDEX IDX_revisions_PERM_GROUP_LIST ON doc_revisions;
ALTER TABLE doc_revisions ADD FULLTEXT IDX_revisions_PERM_GROUP_LIS(PERM_GROUP_LIST)

Mqsql事务

转至菜鸟教程
MySQL事务主要是用于处理操作量大、复杂度高的数据，比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除该人员相关的信息，如信箱、文章等等，这样。这些数据库操作语句就构成一个事务！
注意:

• 在Mysql中只有使用了Innodb数据库引擎的数据库或表才支持事务
• 事务处理可以用来维护数据库的完整性，保证成批额的SQL语句要么全部执行，要么全部不执行
• 事务用来管理 insert,update,delete语句

一般来说，事务必须满足4个条件(ACID)
      原子性(Atomicity,或称不可分割性)
      一致性(Consistency)
      隔离性(Isolation，又称独立性)
      持久性 (Durability)
原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性: 在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性:据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。这四个隔离级别又俗称：脏读、不可重复读、幻读、加锁读。

• 脏读： 就是A事务修改了一个字段信息，但是还没有提交，这个时候B事务来读取这个字段信息，读到的是A事务修改后的，如果此时，A事务回滚了，那么B事务之前读到的这个信息就是脏读。
• 不可重复读： 就是同一个事务里面，读取同一个字段信息，这一刻读到的和下一刻读到的不一样。就比如上面的例子，B事务一开始读到了A事务修改后的信息，然后A回滚，B再次读这个字段，那么信息就和之前不一样，这就是不可重复读。
• 幻读： 这个和脏读有点类似，就是A事务插入了一些数据，但是还没提交，然后B事务读取了这些数据，然后A事务发生了回滚，B事务再次读取，发现和刚刚读取的不一样了，就和出现了幻觉一样，所以就叫幻读。和脏读的区别在于，脏读关注的是更新，幻读关注的是插入。
• 加锁读： 就是指事务会将读取到的信息锁起来，就是在读或者进行其它操作的时候，不允许其它事务进行修改。

持久性:事务处理结束后，对数据的修改是永久的，即便系统故障也不会丢失

MYSQL 事务处理主要有两种方法：
1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

Mysql事务与@Transactional注解的属性

转自简书 简书-跟我回家_f429-@Transactional注解

public @interface Transactional {
    @AliasFor("transactionManager")
    String value() default ""; //当配置多个事务管理器时，可以使用该属性指定选择哪个事务管理器。

    @AliasFor("value")
    String transactionManager() default ""; //事务控制

    Propagation propagation() default Propagation.REQUIRED;//用于指定事务的传播行为、默认值为 REQUIRED。

    Isolation isolation() default Isolation.DEFAULT;//用于指定事务的隔离规则，默认值为DEFAULT

    int timeout() default -1;//用于设置事务的超时属性

    boolean readOnly() default false;//用于设置事务是否只读属性

    Class<? extends Throwable>[] rollbackFor() default {};//用于设置哪些异常需要回滚

    String[] rollbackForClassName() default {};//用于设置哪些异常需要回滚

    Class<? extends Throwable>[] noRollbackFor() default {};//用于设置哪些异常不需要回滚

    String[] noRollbackForClassName() default {};//用于设置哪些异常不需要回滚
}

Mysql常用存储引擎

1、MyISAM引擎；2、MyISAM Merge引擎；3、InnoDB引擎；4、memory引擎；5、archive引擎。

MyISAM与InnoDB 的区别

转自 CSDN-Chackca-MyISAM与InnoDB 的区别（9个不同点）

• InnoDB支持事务，MyISAM不支持事务

• InnoDB支持外键，MyISAM不支持

• MyISAM表格可以被压缩后进行查询操作

• InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁

• InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有

• Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI

    Innodb：frm是表定义文件，ibd是数据文件
    Myisam：frm是表定义文件，myd是数据文件，myi是索引文件
  

• InnoDB是聚集索引，MyISAM是非聚集索引

   聚集索引和非聚集索引的区别
  

  聚集缩索引的B+树上存放的是具体的数据，非聚集索引的叶子节点上存放的是聚集引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据。

  聚集索引插入数据时速度要慢（时间花费在“物理存储的排序”上，也就是首先要找到位置然后插入），查询数据比非聚集数据的速度快。

  php中文网-mysql聚集索引和非聚集索引的区别是什么
  CSDN-码虫码农-简单理解聚集索引和非聚集索引

• InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）

Mysql视图、触发器

视图

1️⃣ 什么是视图：

mysql的视图是mysql数据库中存放数据的一个接口，也可以说是虚拟的表，这些数据可以是一个或几个基本表或视图的数据，也可以是自己定义的数据，当基本表里面的数据发生变化时时，视图里面的数据随之变动

2️⃣ 基本语法

    CREATE[OR REPLACE] VIEW viewname[columnlist]   
        AS SELECT statement 

1. CREATE表示创建新的视图；
2. REPLACE表示替换已经创建的视图；
3. viewname为视图的名称；
4. columnlist为属性列；
5. SELECT statement表示SELECT语句。与单表上创建视图不同的是，SELECT子句是涉及到多表的联合查询语句。

2.1 算法类型

1. MERGE：会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。
2. TEMPTABLE：视图的结果将被置于临时表中，然后使用它执行语句。
3. UNDEFINED：MySQL将选择所要使用的算法。如果可能，它倾向于MERGE而不是TEMPTABLE，这是因为MERGE通常更有效，而且如果使用了临时表，视图是不可更新的。

3️⃣ Navicat创建简单的视图

---

触发器

1️⃣ 什么是触发器(trigger)：

监视某种情况，并触发某种操作,例如当对一个表进行操作（ insert，delete， update）时就会激活它执行，触发器分insert、update、delete触发器。

2️⃣ 触发器语法四要素

1. 监视地点(table)
2. 监视事件(insert/update/delete)
3. 触发时间(after/before)
4. 触发事件(insert/update/delete)

3️⃣ 触发器简单语法

	// 基础用法
    create trigger 触发器名
    [after/before] [insert/delete/update] on 表名
    for each row
    触发器执行的语句块  

	// IF的使用
   if 判断条件 then 
       SQL语句
   	end if
	
	// NEW OLD
	NEW 表示update、insert中更新、插入后的数据
	OLD 表示update、insert中更新、删除前的数据

4️⃣ 写一个简单的触发器

4.1 Navicat创建一个触发器

4.2 新增一条数据，成功触发！

5️⃣ 相关报错合集
CSDN-Late whale-mysql 触发器错误 Can‘t update table in stored function/trigger because it is already used by state（已解决）

存储过程

1️⃣ 循环插入数据 (万能公式) + 利用存储过程操作其他数据并for循环操作

-- 存储过程是一组为了完成特定功能的SQL 语句集
/* ？ 循环插入10W条数据 */

        -- 如果存在同名存储过程则删除.
        DROP PROCEDURE IF EXISTS my_insert;

        -- 创建存储过程
        CREATE PROCEDURE my_insert()
        BEGIN
           DECLARE n int DEFAULT 0;
                loopname:LOOP
                    -- 这里放插入语句
                    -- 获取上一步insert生成的id SET @id = LAST_INSERT_ID(); 第二个insert value里面放@id
                   
                    SET n=n+1;
                IF n=100000 THEN
                    LEAVE loopname;
                END IF;
                END LOOP loopname;
        END;

        -- 执行存储过程
        CALL my_insert();

-- 删除存储过程
DROP PROCEDURE my_insert





-- 运行存储过程：添加所有用户到ALL用户组里
-- 存在則刪除
DROP PROCEDURE IF EXISTS add_allUser;
-- 创建存储过程
CREATE PROCEDURE add_allUser()
BEGIN
DECLARE theUserName VARCHAR(100);
DECLARE isDone INT DEFAULT 0;
DECLARE isSystem INT DEFAULT 0;
-- 获取对应数据并声明为组
DECLARE allUserName CURSOR FOR SELECT USERNAME FROM adm_users WHERE USER_TYPE = 'Internal';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET isDone = 1;
OPEN allUserName;
-- 开始循环数据
REPEAT
	FETCH allUserName INTO theUserName;
	SET isSystem = 0;
	IF theUserName = 'sysadmin' THEN SET isSystem = 1;
	END IF;
	IF NOT isDone THEN
	INSERT INTO `adm_user_group_rel` (`GROUP_NAME`, `USERNAME`, `IS_SYSTEM`) VALUES ('ALL', theUserName, isSystem);
	END IF;
UNTIL isDone END REPEAT;
CLOSE allUserName;
END;

CALL add_allUser;

DROP PROCEDURE add_allUser;