【数据库】MySQL + Sequel Pro + 报错总结

admin 管理员组

文章数量: 887021

【数据库】SQL语法
https://blog.csdn/weixin_42915286/article/details/85339284
【数据库】数据库原理
https://blog.csdn/weixin_42915286/article/details/83028265

安装路径：
/usr/local/mysql

MySQL

MySQL是什么？
My是MySQL的联合创始人 - Monty Widenius 的女儿的名字。MySQL是My和SQL的组合，这就是MySQL命名的由来。
MySQL的官方网址： http://www.mysql/ ；
MySQL的社区版本下载地址为： http://dev.mysql/downloads/mysql/
MySQL是一个数据库管理系统，也是一个关系数据库。 它是由Oracle支持的开源软件。这意味着任何一个人都可以使用MySQL而不用支付一毛钱。 另外，如果需要，还可以更改其源代码或进行二次开发以满足您的需要。
即使MySQL是开源软件，但是可以从Oracle购买商业许可证版本，以获得高级支持服务(特殊企业用户需要)。
与其他数据库软件(如Oracle数据库或Microsoft SQL Server)相比，MySQL非常容易学习和掌握。
MySQL可以在各种平台上运行UNIX，Linux，Windows等。可以将其安装在服务器甚至桌面系统上。
此外，MySQL是可靠，可扩展和快速的。如果您开发网站或Web应用程序，MySQL是一个不错的选择(强烈建议使用)。
MySQL是LAMP堆栈的重要组成部分，包括Linux，Apache，MySQL和PHP。

————————————————————
SQL语言共分为四大类：
数据【查询】语言【DQL】 (Data Query Language)
数据【操纵】语言【DML】 (Data Manipulation Language)
数据【定义】语言【DDL】 (Data Definition Language)
数据【控制】语言【DCL】 (Data Control Language)

1. 数据查询语言 DQL
   数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE
   子句组成的查询块：
   SELECT <字段名表>
   FROM <表或视图名>
   WHERE <查询条件>

2. 数据操纵语言 DML
   数据操纵语言DML主要有三种形式：

1. 插入：INSERT
2. 更新：UPDATE
3. 删除：DELETE

3. 数据定义语言 DDL
   （作用：建立数据库对象）
   数据定义语言DDL用来创建数据库中的各种对象：表、视图、索引、同义词、聚簇等如：
   CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
   | | | | |
   表 视图 索引 同义词 簇

DDL操作是隐性提交的！不能rollback

4. 数据控制语言 DCL
   数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制
   数据库操纵事务发生的时间及效果，对数据库实行监视等。如：

1. GRANT：授权。

2. ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。
   回滚—ROLLBACK
   回滚命令使数据库状态回到上次最后提交的状态。其格式为：
   SQL>ROLLBACK;

3. COMMIT [WORK]：提交。
   在数据库的插入、删除和修改操作时，只有当事务在提交到数据
   库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看
   到所做的事情，别人只有在最后提交完成后才可以看到。
   提交数据有三种类型：显式提交、隐式提交及自动提交。下面分
   别说明这三种类型。

(1) 显式提交
用COMMIT命令直接完成的提交为显式提交。其格式为：
SQL>COMMIT；

(2) 隐式提交
用SQL命令间接完成的提交为隐式提交。这些命令是：
ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，
EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

(3) 自动提交
若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，
系统将自动进行提交，这就是自动提交。其格式为：
SQL>SET AUTOCOMMIT ON；
————————————————————

————————————————————

提问：数据库三范式？
1NF：关系模式R的所有属性都不能再分解成更基本的数据单位；
2NF：满足1NF，且R所有非主属性都完全依赖与R的每一个候选关键属性；
3NF：满足1NF和2NF，X是R的仁义属性集，若X非传递依赖于R的任意一个候选关键字；

提问：一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 mysql 数据库，又插入了一条数据，此时 id 是几？

答：6
一般情况下，我们创建的表的类型是InnoDB:
如果新增一条记录（不重启mysql的情况下），这条记录的id是8；
但是如果【重启】MySQL的话，这条记录的ID是6。
因为InnoDB表只把自增主键的最大ID记录到内存中，所以重启数据库或者对表OPTIMIZE操作，都会使最大ID丢失。
但是，如果我们使用表的类型是MylSAM；
那么这条记录的ID就是8。因为MylSAM表会把自增主键的最大ID记录到数据文件里面，重启MYSQL后，自增主键的最大ID也不会丢失。

提问：如何获取当前数据库版本？？
mysql>select version();

提问：ACID 是什么？

1.Atomicity 原子性
原子性，指的是整个事务是一个独立的单元，是不可分割的工作单位，要么操作成功，要么操作不成功，事务必须要保持和系统处于一致的状态，只有使据库中所有的操作执行成功，才算整个事务成功；事务中任何一个SQL语句执行失败，那么已经执行成功的SQL语句也必须撤销，数据库状态应该退回到执行事务前的状态。
2.Consistency 一致性
指数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。例如对银行转帐事务，不管事务成功还是失败，应该保证事务结束后ACCOUNTS表中Tom和Jack的存款总额为2000元。
3.Isolation 隔离性
指的是在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。
4.Durability 持久性
指的是只要事务成功结束，它对数据库所做的更新就必须永久保存下来。即使发生系统崩溃，重新启动数据库系统后，数据库还能恢复到事务成功结束时的状态。

提问：char 和 varchar 的区别是什么？

char：定长，效率高，一般用于固定长度的表单提交数据存储 ；
例如：身份证号，手机号，电话，密码等
varchar：不定长，效率偏低
存数据时的区别
char定义的是固定长度，长度范围为0-255，存储时，如果字符数没有达到定义的位数，会在后面用空格补全存入数据库中，在上例中，name实际存储在数据中的数据为’zejin ‘；
varchar是变长长度，长度范围为0-65535，存储时，如果字符没有达到定义的位数，也不会在后面补空格，在上例subject字段中，实际存储在数据中的数据为’zejin’，当然还有一或两个字节来描述该字节长度；
取数据时的区别
数据库取char的数据时，会把后面的空格全部丢弃掉；
而数据库在取varchar数据时，尾部空格会保留；

提问：float 和 double 的区别是什么？

double精度高，有效数字16位，float精度7位。但double消耗内存是float的两倍，double的运算速度比float慢得多；
float数值类型用于表示单精度浮点数值，而double数值类型用于表示双精度浮点数值，float和double都是浮点型，而decimal是定点型；
MySQL 浮点型和定点型可以用类型名称后加（M，D）来表示，M表示该值的总共长度，D表示小数点后面的长度，M和D又称为精度和标度，如float(7,4)的 可显示为-999.9999，MySQL保存值时进行四舍五入，如果插入999.00009，则结果为999.0001。
FLOAT和DOUBLE在不指 定精度时，默认会按照实际的精度来显示，而DECIMAL在不指定精度时，默认整数为10，小数为0。

提问：mysql 的内连接、左连接、右连接有什么区别？？

1.内连接,显示两个表中有联系的所有数据;
2.左连接,以左表为参照，显示所有数据;
3.右连接,以右表为参照，显示所有数据;

提问：mysql 索引是怎么实现的？

索引是一个【排序】的列表，在这个列表中存储着【索引的值】和包含这个值的数据【所在行】的【物理地址】；
在数据十分庞大的时候，索引可以大大加快查询的速度；这是因为使用索引后可以【不用扫描全表】来定位某行的数据，而是先通过【索引表】找到该行数据对应的【物理地址】然后访问相应的数据；

优势：
可以快速检索，减少I/O次数，加快检索速度；根据索引分组和排序，可以加快分组和排序；
劣势：
索引【本身也是表】，因此会占用【存储空间】，一般来说，索引表占用的空间的数据表的【1.5倍】；
索引表的【维护和创建】需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作（增删改）的效率，因为在修改数据表的同时还需要修改索引表；

索引的分类
常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引
1、主键索引：即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值；
2、唯一索引：用来建立索引的列的值必须是唯一的，允许空值
3、普通索引：用表中的普通列构建的索引，没有任何限制
4、全文索引：用大文本对象的列构建的索引（下一部分会讲解）
5、组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值

提问：什么时候要使用索引？

【主键】自动建立唯一索引；
经常作为查询条件在WHERE或者ORDER BY语句中出现的列要建立索引；
作为【排序的列】要建立索引；
查询中【与其他表关联】的字段，外键关系建立索引
【高并发】条件下倾向【组合索引】；
用于【聚合函数的列】可以建立索引：例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引

• 什么时候不要使用索引？
  【经常增删改】的列不要建立索引；
  有【大量重复的列】不建立索引；
  【表记录太少】不要建立索引。只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快–不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。

• 索引失效的情况：
  在【组合索引】中不能有列的值为NULL，如果有，那么这一列对组合索引就是无效的。
  在一个SELECT语句中，【索引只能使用一次】：如果在WHERE中使用了，那么在ORDER BY中就不要用了。
  LIKE操作中，'%aaa%'不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引。
  在索引的列上使用表达式或者函数会使索引失效，
  例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。
  其它通配符同样，也就是说，在查询条件中使用【正则表达式】时，只有在搜索模板的【第一个字符不是通配符】的情况下才能使用索引。
  在查询条件中使用不等于，包括<符号、>符号和！=会导致【索引失效】。特别的是如果对主键索引使用！=则不会使索引失效，如果对主键索引或者整数类型的索引使用<符号或者>符号不会使索引失效。
  在查询条件中使用IS NULL或者IS NOT NULL会导致索引失效。
  字符串【不加单引号】会导致索引失效：更准确的说是类型不一致会导致失效，比如字段email是字符串类型的，使用WHERE email=99999则会导致失败，应该改为WHERE email='99999'。
  在【查询条件】中使用OR连接多个条件会导致索引失效，除非OR链接的每个条件都加上索引，这时应该改为两次查询，然后用UNION ALL连接起来。
  如果【排序的字段】使用了索引，那么【select的字段也要是索引字段】，否则【索引失效】；
  特别的是如果排序的是主键索引则select *也不会导致索引失效。
  【尽量不要包括多列排序】，如果一定要，最好为这队列构建【组合索引】；

提问：怎么验证 mysql 的索引是否满足需求？

在select语句前加上explain，运行，查看key那一列有没有用到所用的索引；

explain显示了MySQL如何使用索引来处理select语句以及连接表
possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引；

提问：说一下数据库的事务隔离？

未提交读(Read Uncommitted)：
允许脏读，也就是可能读取到其他会话中未提交事务修改的数据；

提交读(Read Committed)：
只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)；

可重复读(Repeated Read)：
可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读；

串行读(Serializable)：
完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞；

① 脏读:
脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

② 不可重复读:
是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

④ 幻读:
第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

提问：说一下 mysql 常用的引擎？

Server version: 8.0.15中：
mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

更改默认引擎：
在配置文件my.ini中的[mysqld]下面加入default-storage-engine=INNODB

a.Innodb引擎
MySQL 8.0的默认引擎，他提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
它本身实际上是基于Mysql后台的完整的系统。Mysql运行的时候，Innodb会在内存中建立缓冲池，用于缓冲数据和索引。
但是，该引擎是不支持全文搜索的。同时，启动也比较的慢，它是不会保存表的行数的。当进行Select count(*) from table指令的时候，需要进行扫描全表。
所以当需要使用数据库的事务时，该引擎就是首选。由于锁的粒度小，写操作是不会锁定全表的。所以在并发度较高的场景下使用会提升效率的。

b.MyIASM引擎
不提供事务的支持，也不支持行级锁和外键；
因此当执行Insert和Update操作时，即执行写操作的时候需要锁定这个表。所以会导致效率会降低。
不过和Innodb不同的是，MyIASM引擎是保存了表的行数，于是当进行Select count(*) from table语句时，可以直接的读取已经保存的值而不需要进行扫描全表。
所以， 如果表的读操作远远多于写操作时，并且不需要事务的支持的。可以将MyIASM作为数据库引擎的首先。

提问：mysql 的行锁和表锁？

表级锁：
每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行级锁：
每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

提问：乐观锁和悲观锁？

白话：
有人想去足浴，【乐观锁】认为XX技师应该在上班，于是走到店门口再问前台（乐观锁非数据库自带，需自己去实现）：XX技师在吗？
【悲观锁】认为XX技师应该不在，在家就用自己的电话（数据库本身就有悲观锁）问：确认XX技师在吗？

总结：

1.悲观锁使用了排他锁（写锁），当程序独占锁时，其他程序就连查询都是不允许的，导致吞吐较低。如果在查询较多的情况下，可使用乐观锁。
2.乐观锁更新有可能会失败，甚至是更新几次都失败，这是有风险的。所以如果写入较频繁，对吞吐要求不高，可使用悲观锁。
读多的时候，即冲突真的很少发生的时候，用乐观锁，省去了锁的开销，加大了系统整个吞吐量；如果经常产生冲突，上层应用会不断retry，这样反而降低了性能，所以适合用悲观锁；
也就是一句话：【读频繁用乐观锁，写频繁用悲观锁】。

乐观锁
乐观锁假设认为数据一般情况下不会造成冲突，每次拿数据（读）的时候都认为别人不会修改，所以不会上锁，在数据进行【提交/更新】时，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何处理或者程序自动去重试；
【场景】：【写少读多，提高系统吞吐量】；（读多）
【样例】：数据库乐观锁、缓存乐观锁、write_condition机制；

乐观锁不是数据库自带的，需要我们自己去实现。
乐观锁是指操作数据库时(更新操作)，想法很乐观，认为这次的操作不会导致冲突，在操作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了。
通常实现是这样的：
在表中的数据进行操作时(更新)，先给数据表加一个版本(version)字段，每操作一次，将那条记录的版本号加1。也就是先查询出那条记录，获取出version字段,如果要对那条记录进行操作(更新),则先判断此刻version的值是否与刚刚查询出来时的version的值相等，如果相等，则说明这段期间，没有其他程序对其进行操作，则可以执行更新，将version字段的值加1；如果更新时发现此刻的version值与刚刚获取出来的version的值不相等，则说明这段期间已经有其他程序对其进行操作了，则不进行更新操作。

悲观锁
悲观锁对数据被外界修改持保守态度（悲观），每次拿数据（读）的时候都认为别人会修改，因此在整个数据处理过程（读）时，将数据处于锁定状态，往往依靠数据库提供的锁机制实现；
【场景】：【写多读少，保证数据安全】；（写多）
【样例】：行锁、页锁、表锁、共享锁（读锁）、【排他锁】（写锁）；

与乐观锁相对应的就是悲观锁了。悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行【每次操作】时都要通过获取锁才能进行对相同数据的操作，这点跟java中的synchronized很相似，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。
说到这里，由悲观锁涉及到的另外两个锁概念就出来了，它们就是共享锁与排它锁。共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。

共享锁：select * from table lock in share mode;
排他锁：select * from table for update;

应用场景 案例：
某商品，用户购买后库存数应-1，而某两个或多个用户同时购买，此时三个执行程序均同时读得库存为n，之后进行了一些操作，最后将均执行update table set 库存数=n-1，那么，很显然这是错误的。

解决：

1.使用悲观锁（其实说白了也就是排他锁）
|–程序A在查询库存数时使用排他锁（select * from table where id=10 for update）
|–然后进行后续的操作，包括更新库存数，最后提交事务。
|–程序B在查询库存数时，如果A还未释放排他锁，它将等待。
|–程序C同B……
2.使用乐观锁（靠表设计和代码来实现）
|–一般是在该商品表添加version版本字段或者timestamp时间戳字段
|–程序A查询后，执行更新变成了：
update table set num=num-1 where id=10 and version=23
这样，保证了修改的数据是和它查询出来的数据是一致的，而其他执行程序未进行修改。当然，如果更新失败，表示在更新操作之前，有其他执行程序已经更新了该库存数，那么就可以尝试重试来保证更新成功。为了尽可能避免更新失败，可以合理调整重试次
数（阿里巴巴开发手册规定重试次数不低于三次）。

提问：mysql 问题排查方法？

事物级别
select @@global.tx_isolation;

输出数据当前状态（返回最近一次死锁场景，等等）
可用于排查死锁问题，锁定行数等问题
SHOW ENGINE INNODB STATUS ;

查询事务信息
（观察事务启动时间，判断是否为最近的创建的）
select * from information_schema.INNODB_TRX;

查询数据库锁等待信息
（如果存在数据表示当前存在所等待情况）
select * from information_schema.INNODB_LOCK_WAITS;

手动杀掉某个进程
（来源于select * from information_schema.INNODB_TRX;）
kill trx_mysql_thread_id;
https://blog.csdn/weixin_34220179/article/details/88160163

提问：？

Myf

我们需要重点关注[mysqld]

Spring Boot 配置MySQL：

spring:
  datasource:
    driver: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/数据库名?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
    username: root
    password: XXXX

GMT%2B8 = +8 东八区

MySQL版本问题

MySQL版本问题看似简单，其实非常重要，面试中也常问到，我们必须了解每个版本的区别；
本人使用的是Server version: 8.0.15

面试常见问题有：

• 1.工作中使用的是什么版本的MySQL？为什么用这个版本？
  “ 公司要我用哪个我就用哪个 ” 是大忌；其实考察的是常见版本有哪些？优缺点？

• 2.如何决定是否要对MySQL进行升级，如何升级？
  考察是否对MySQL有过升级，升级操作需要非常谨慎，不能为升级而升级，需要大量测试；

• 3.最新的MySQL版本是什么？他的什么特性最吸引你？
  考察对MySQL社区的关注度，以及快速学习能力；（新版本也许不用马上使用，但必须有个大致了解）

• MySQL常见的发行版

1.官方社区版本（ORACLE）࿱

本文标签： 报错 数据库 Sequel MySQL Pro