mysql常见错误问题

项目

创表要求

1. 满足三范式：所有字段不可再分，字段不能部分依赖与主键，字段不能间接有依赖与主键
2. 在一个表中所有的字段信息只与创建的表有关，不得含有与表名和创建表目的毫无关联的字段存在
3. 一般存在单独一个表中用来关联两个表(两个表处于多对多关系)
4. 若两个表之间关系是一对一关系则可以不用创建关联表

基础

判断表是否存在

create table if not exists table_name();

事务的四大特性

原子性：事务的进行要么全部成功，要么全部失败，不会停在中间某个阶段

一致性：事务的执行前后，数据库的完整性没有被破坏

隔离性：数据库运行多个事务并发的对数据进行读写增改，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致

持久性：事务提交后，对数据的操作是永久的不变的

临时表的操作

• MySQL 临时表在我们需要保存一些临时数据时是非常有用的。
• 临时表只在当前连接可见，当关闭连接时，MySQL 会自动删除表并释放所有空间。
• 在 MySQL 中，临时表是一种在当前会话中存在的表，它在会话结束时会自动被销毁。
• MySQL 临时表只在当前连接可见，如果你使用PHP脚本来创建 MySQL 临时表，那每当 PHP 脚本执行完成后，该临时表也会自动销毁。
• 如果你使用了其他 MySQL 客户端程序连接 MySQL 数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然你也可以手动销毁。

-- 创建临时表

CREATE TEMPORARY TABLE temp_orders AS SELECT * FROM orders WHERE order_date >= '2023-01-01';

-- 查询临时表

SELECT * FROM temp_orders;

-- 插入数据到临时表

INSERT INTO temp_orders (order_id, customer_id, order_date) VALUES (1001, 1, '2023-01-05');

-- 查询临时表

SELECT * FROM temp_orders;

-- 删除临时表

DROP TEMPORARY TABLE IF EXISTS temp_orders;

事务操作时什么操作不可回滚

事务中无法回滚的核心是DDL 操作和触发隐式提交的操作，因为它们会强制提交事务或本身不支持事务特性。

DDL 操作（如 CREATE、ALTER、DROP、TRUNCATE 等）会自动提交事务，且操作本身不可回滚。

原因是 DDL 操作会修改数据库的元数据（如表结构、索引信息），MySQL 为保证元数据一致性，执行 DDL 时会强制提交当前事务，并开启新事务，因此无法通过 ROLLBACK 撤销。

TRUNCATE 用于清空表数据，虽然功能类似 DELETE，但它属于 DDL 操作（会重置表的自增 ID、释放存储空间），执行后立即生效，无法回滚。

隐式提交操作

• 执行 DDL 语句（如前所述）；
• 执行 LOCK TABLES、UNLOCK TABLES（锁定 / 解锁表）；
• 执行 SET AUTOCOMMIT = 1（开启自动提交，默认值）；
• 退出数据库连接（正常断开时会自动提交未完成事务）。

MySQL复制表

方法一：

1.首先show create table 表名;//得到建表语句

2.通过上述的建表语句修改表名即可复制表结构和字段。

3.如果还要复制表数据，通过：insert into 表 select...

方法二：

使用 mysqldump 命令 mysqldump 命令可以用于备份和还原 MySQL 数据库。 如果你只想复制一张表，你可以使用 mysqldump 导出该表的结构和数据， 然后再导入到新的数据库或新的表中。

以下是一个简单的实例。

备份表数据： mysqldump -u username -p dbname old_table > old_table_dump.sql 这将导出名为 old_table 的表的结构和数据到一个名为 old_table_dump.sql 的 SQL 文件。

你需要提供 MySQL 用户名和密码，并替换 username、dbname 和 old_table 为实际的值。

还原到新的数据库： mysql -u username -p new_dbname < old_table_dump.sql 这将在新的数据库（new_dbname）中创建一个名为 old_table 的表，并将之前导出的结构和数据导入到新表中。

方法三：

//复制表结构 create table 表名 like 表名;

//为上一步创建的表增加数据 insert into 表名 select * from 表名;

//复制表数据不能复制表数据（加筛选条件就可以复制部分信息）

create table 表名 select * from 被复制表名；

进阶

锁的分类

mysql锁的介绍笔记

聚集索引和二级索引的区别

分类	含义	特点
聚集索引(Clustered Index)	将数据存储与索引放一块，索引结构的叶子节点保存了行数据	必须有，而且只有一个
二级索引(Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则：

• 如果存在主键，主键索引就是聚集索引
• 如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引
• 如果表没有主键或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引

最左前缀法则

如果索引关联了多列（联合索引），要遵守最左前缀法则，最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。

如果跳跃某一列，索引将部分失效（后面的字段索引失效）。

联合索引中，出现范围查询（），范围查询右侧的列索引失效。可以用>=或者

索引失效的情况

1. 在索引列上进行运算操作，索引将失效。如：explain select * from tb_user where substring(phone, 10, 2) = '15';
2. 字符串类型字段使用时，不加引号，索引将失效。如：explain select * from tb_user where phone = 17799990015;，此处phone的值没有加引号
3. 模糊查询中，如果仅仅是尾部模糊匹配，索引不会是失效；如果是头部模糊匹配，索引失效。如：explain select * from tb_user where profession like '%工程';，前后都有 % 也会失效。
4. 用 or 分割开的条件，如果 or 其中一个条件的列没有索引，那么涉及的索引都不会被用到。
5. 如果 MySQL 评估使用索引比全表更慢，则不使用索引。

explain

EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

语法：

# 直接在select语句之前加上关键字 explain / desc

EXPLAIN SELECT 字段列表 FROM 表名 HWERE 条件;

EXPLAIN 各字段含义：

• id：select 查询的序列号，表示查询中执行 select 子句或者操作表的顺序（id相同，执行顺序从上到下；id不同，值越大越先执行）
• select_type：表示 SELECT 的类型，常见取值有 SIMPLE（简单表，即不适用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等
• type：表示连接类型，性能由好到差的连接类型为 NULL、system、const、eq_ref、ref、range、index、all
• possible_key：可能应用在这张表上的索引，一个或多个
• Key：实际使用的索引，如果为 NULL，则没有使用索引
• Key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
• rows：MySQL认为必须要执行的行数，在InnoDB引擎的表中，是一个估计值，可能并不总是准确的
• filtered：表示返回结果的行数占需读取行数的百分比，filtered的值越大越好、

获得服务器元数据

MySQL 元数据是关于数据库和其对象（如表、列、索引等）的信息。

命令	描述
SELECT VERSION()	服务器版本信息
SELECT DATABASES()	当前数据库名（或者返回空）
SELECT USER()	当前用户名
SHOW STATUS	服务器状态
SHOW VARIABLES	服务器配置变量