MySQL相关

发表于 | 更新于 | 分类于 | 阅读次数：

MySQL相关知识

总结积累MySQL相关原理和操作。

1. 原理

2. 基本操作

2.1 建库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
DROP DATABASE IF EXISTS sampledb;
CREATE DATABASE sampledb DEFAULT CHARACTER SET utf8;
USE sampledb;


/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     2016-10-3 0:34:13                             */
/*==============================================================*/


drop table if exists t_board;

drop table if exists t_board_manager;

drop table if exists t_post;

drop table if exists t_topic;

drop table if exists t_user;

drop table if exists t_login_log;

CREATE TABLE `t_board` (
  `board_id` int(11) NOT NULL auto_increment COMMENT '论坛版块ID',
  `board_name` varchar(150) NOT NULL default '' COMMENT '论坛版块名',
  `board_desc` varchar(255) default NULL COMMENT '论坛版块描述',
  `topic_num` int(11) NOT NULL default '0' COMMENT '帖子数目',
  PRIMARY KEY  (`board_id`),
  KEY `AK_Board_NAME` (`board_name`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

#
# Dumping data for table t_board
#

INSERT INTO `t_board` VALUES (1,'SpringMVC','Spring 框架提供了构建 Web 应用程序的全功能 MVC 模块\r\n',8);
INSERT INTO `t_board` VALUES (2,'\r\nSpring Boot','简化新Spring应用的初始搭建以及开发过程，让我们一起来深入这个领域吧',0);
INSERT INTO `t_board` VALUES (3,'Spring 事务管理','本板块将讨论 Spring 庞杂而强大的事务功能,包括编程式事务和声明式事务 ',0);
INSERT INTO `t_board` VALUES (4,' IOC和AOP ','IOC和AOP讨论板块',3);
INSERT INTO `t_board` VALUES (7,'dddddddddddd','ddddddddddddddddddddddddddddddd',0);
INSERT INTO `t_board` VALUES (8,'SpringMVC','SpringMVC经验~~',0);

#
# Source for table t_board_manager
#

CREATE TABLE `t_board_manager` (
  `board_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY  (`board_id`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='论坛管理员';

#
# Dumping data for table t_board_manager
#

INSERT INTO `t_board_manager` VALUES (1,1);
INSERT INTO `t_board_manager` VALUES (1,2);
INSERT INTO `t_board_manager` VALUES (5,2);
INSERT INTO `t_board_manager` VALUES (5,3);

#
# Source for table t_login_log
#

CREATE TABLE `t_login_log` (
  `login_log_id` int(11) NOT NULL auto_increment,
  `user_id` int(11) default NULL,
  `ip` varchar(30) NOT NULL default '',
  `login_datetime` varchar(30) NOT NULL,
  PRIMARY KEY  (`login_log_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Dumping data for table t_login_log
#


#
# Source for table t_post
#

CREATE TABLE `t_post` (
  `post_id` int(11) NOT NULL auto_increment COMMENT '帖子ID',
  `board_id` int(11) NOT NULL default '0' COMMENT '论坛ID',
  `topic_id` int(11) NOT NULL default '0' COMMENT '话题ID',
  `user_id` int(11) NOT NULL default '0' COMMENT '发表者ID',
  `post_type` tinyint(4) NOT NULL default '2' COMMENT '帖子类型 1:主帖子 2:回复帖子',
  `post_title` varchar(50) NOT NULL COMMENT '帖子标题',
  `post_text` text NOT NULL COMMENT '帖子内容',
  `create_time` date NOT NULL COMMENT '创建时间',
  PRIMARY KEY  (`post_id`),
  KEY `IDX_POST_TOPIC_ID` (`topic_id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8 COMMENT='帖子';

#
# Dumping data for table t_post
#

INSERT INTO `t_post` VALUES (1,1,1,1,1,'SpringMVC','Spring Web MVC是一种基于Java的实现了Web MVC设计模式的请求驱动类型的轻量级Web框架\r\n','2016-03-07');
INSERT INTO `t_post` VALUES (2,1,2,1,1,'配置\r\详解','谁能告诉我SpringMVC的详细配置呢','2016-03-07');
INSERT INTO `t_post` VALUES (3,1,3,1,1,'test3','http://localhost/forum/boardManage.do?method=addTopicPage&boardId=1','2016-03-16');
INSERT INTO `t_post` VALUES (4,1,4,1,1,'test5','http://localhost/forum/boardManage.do?method=addTopicPage&boardId=1','2016-03-16');
INSERT INTO `t_post` VALUES (5,4,5,1,1,'AOP背后的故事','AOP背后的故事\r\n','2016-04-16');
INSERT INTO `t_post` VALUES (6,0,5,1,2,'铁杵磨成针','铁杵磨成针............\r\n','2016-04-16');
INSERT INTO `t_post` VALUES (7,4,6,1,1,'IOC的原理一','IOC的原理深入讲解\r\n','2016-04-16');
INSERT INTO `t_post` VALUES (8,4,7,1,1,'IOC的原理二','IOC的原理深入讲解','2016-04-16');
INSERT INTO `t_post` VALUES (14,5,14,1,1,'d','dddddddddddddd','2016-04-12');
INSERT INTO `t_post` VALUES (15,5,15,1,1,'dad','sdfffffffffffffffffff','2016-04-12');
INSERT INTO `t_post` VALUES (20,1,20,1,1,'测试。。。。','测试。。。。','2016-04-17');
INSERT INTO `t_post` VALUES (21,1,21,1,1,'测试。。。。','测试。。。。','2016-04-17');
INSERT INTO `t_post` VALUES (22,1,22,1,1,'SpringMVC集成','SpringMVC集成！！','2016-04-18');
INSERT INTO `t_post` VALUES (23,1,23,1,1,'SpringMVC集成','SpringMVC集成！！','2016-04-18');
INSERT INTO `t_post` VALUES (24,1,1,1,2,'SpringMVC集成','SpringMVC集成！！','2016-04-18');

#
# Source for table t_topic
#

CREATE TABLE `t_topic` (
  `topic_id` int(11) NOT NULL auto_increment COMMENT '帖子ID',
  `board_id` int(11) NOT NULL COMMENT '所属论坛',
  `topic_title` varchar(100) NOT NULL default '' COMMENT '帖子标题',
  `user_id` int(11) NOT NULL default '0' COMMENT '发表用户',
  `create_time` date NOT NULL COMMENT '发表时间',
  `last_post` date NOT NULL COMMENT '最后回复时间',
  `topic_views` int(11) NOT NULL default '1' COMMENT '浏览数',
  `topic_replies` int(11) NOT NULL default '0' COMMENT '回复数',
  `digest` int(11) NOT NULL COMMENT '0:不是精华话题 1:是精华话题',
  PRIMARY KEY  (`topic_id`),
  KEY `IDX_TOPIC_USER_ID` (`user_id`),
  KEY `IDX_TOPIC_TITLE` (`topic_title`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8 COMMENT='话题';

#
# Dumping data for table t_topic
#

INSERT INTO `t_topic` VALUES (1,1,'test',1,'2016-03-07','2016-04-18',0,1,1);
INSERT INTO `t_topic` VALUES (2,1,'test2',1,'2016-03-07','2016-03-07',0,0,1);
INSERT INTO `t_topic` VALUES (3,1,'test3',1,'2016-03-16','2016-03-16',0,0,0);
INSERT INTO `t_topic` VALUES (4,1,'test5',1,'2016-03-16','2016-03-16',0,0,0);
INSERT INTO `t_topic` VALUES (5,4,'AOP背后的故事',1,'2016-03-16','2016-03-16',0,1,1);
INSERT INTO `t_topic` VALUES (6,4,'IOC的原理一',1,'2016-03-16','2016-03-16',0,0,0);
INSERT INTO `t_topic` VALUES (7,4,'IOC的原理二',1,'2016-03-16','2016-03-16',0,0,0);
INSERT INTO `t_topic` VALUES (14,5,'d',1,'2016-04-12','2016-04-12',0,0,0);
INSERT INTO `t_topic` VALUES (15,5,'dad',1,'2016-04-12','2016-04-12',0,0,0);
INSERT INTO `t_topic` VALUES (20,1,'测试。。。。',1,'2016-04-17','2016-04-17',0,0,0);
INSERT INTO `t_topic` VALUES (21,1,'测试。。。。',1,'2016-04-17','2016-04-17',0,0,0);
INSERT INTO `t_topic` VALUES (22,1,'SpringMVC集成',1,'2016-04-18','2016-04-18',0,0,0);
INSERT INTO `t_topic` VALUES (23,1,'SpringMVC集成',1,'2016-04-18','2016-04-18',0,0,0);

#
# Source for table t_user
#

CREATE TABLE `t_user` (
  `user_id` int(11) NOT NULL auto_increment COMMENT '用户Id',
  `user_name` varchar(30) NOT NULL COMMENT '用户名',
  `password` varchar(30) NOT NULL default '' COMMENT '密码',
  `user_type` tinyint(4) NOT NULL default '1' COMMENT '1:普通用户 2:管理员',
  `locked` tinyint(4) NOT NULL default '0' COMMENT '0:未锁定 1:锁定',
  `credit` int(11) default NULL COMMENT '积分',
  `last_visit` datetime default NULL COMMENT '最后登陆时间',
  `last_ip` varchar(20) default NULL COMMENT '最后登陆IP',
  PRIMARY KEY  (`user_id`),
  KEY `AK_AK_USER_USER_NAME` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

#
# Dumping data for table t_user
#

INSERT INTO `t_user` VALUES (1,'tom','1234',2,0,203,NULL,NULL);
INSERT INTO `t_user` VALUES (2,'john','1234',2,1,10,NULL,NULL);
INSERT INTO `t_user` VALUES (3,'ggg','123123',1,0,110,NULL,NULL);

2.2 MySQL按行导入text

1
2
3
mysql --local-infile=1 -u root -p 
use peoples_daily;
load data local infile "/home/mao/Downloads/source_BIO_2014_cropus.csv" into table news_table(news_text);

2.3 设置默认值

The MySQL DEFAULT keyword is a database CONSTRAINT or RULE that is applied when inserting new records into a table. If the value is not provided to any column while inserting a new row in the table, the default value will be used instead. In this article, we will explain the benefits of using MySQL DEFAULT values and show step-by-step applications.

2.3.1 Why use the MySQL DEFAULT value?

  • For most of the data types, MySQL provides ‘NULL’ as a DEFAULT value. In the case of Primary Key columns, these cannot contain ‘NULL’ values. In this case, a DEFAULT unique value is a good way to handle this.
  • For predefined standards or business rules, we can use DEFAULT values instead of manually specifying the field every time.
  • Inserting DATETIME value in a DATE column to identify when the record inserted in a table.

2.3.2 What are the types of DEFAULT values?

  • Implicit: MySQL automatically sets a default value if not provided.For numeric data types, the default is 0. If a column is declared as an integer and with the AUTO_INCREMENT attribute, the default is the next value in the sequence.

  • Explicit: We can explicitly set a DEFAULT value when creating or altering a table.

2.3.3 How to set the DEFAULT value in MySQL?

2.3.3.1 Set DEFAULT value on CREATE TABLE

We can set the DEFAULT value for a column while creating a table using the MySQL DEFAULT constraint.

  • Syntax:
1
2
3
4
CREATE TABLE table_name(
  column_name data_type,
  Column_name data_type DEFAULT ‘ value ’
);
  • Example:
1
2
3
4
5
6
7
8
CREATE TABLE Employee (
  Employee_ID int NOT NULL,
  Last_Name varchar(255) NOT NULL,
  First_Name varchar(255),
  Age int,
  Location varchar(255) DEFAULT 'London',
  Joining_Date DATETIME
);

The MySQL statement above sets a DEFAULT value for the “Location” column upon creation of the “Employee” table.

2.3.3.2 Set DEFAULT value on ALTER TABLE

We can set the DEFAULT value for a column when the table is already created using MySQL DEFAULT constraint.

  • Syntax:
1
2
3
4
ALTER TABLE
  table_name ALTER column_name
SET
  DEFAULT 'value';
  • Example:
1
2
3
4
ALTER TABLE
  Employee ALTER Location
SET
  DEFAULT 'London';

2.3.3.3 How to DROP a DEFAULT constraint in MySQL?

We can also remove an existing MySQL DEFAULT constraint. To drop a DEFAULT value constraint, we use the SQL statement below.

  • Syntax:
1
2
ALTER TABLE
  table_name ALTER column_name DROP DEFAULT;
  • Example:
1
2
ALTER TABLE
  Employee ALTER Location DROP DEFAULT;

2.3.3.4 How to set DEFAULT values to a VARCHAR column in MySQL

  • Step 1: Create a table with “London” as a DEFAULT value for the Location column.
1
2
3
4
5
6
7
8
CREATE TABLE Employee (
  Employee_ID int NOT NULL,
  Last_Name varchar(255) NOT NULL,
  First_Name varchar(255),
  Age int,
  Location varchar * *(255) DEFAULT 'London',
  Joining_Date DATETIME
);
  • Step 2: Insert a row into a table without value for the Location column.
1
2
3
4
5
6
7
8
9
10
INSERT INTO
  Employee(
    Employee_ID,
    Last_Name,
    First_Name,
    Age,
    Joining_Date
  )
VALUES
  (101, 'John', 'Wood', 35, CURRENT_TIMESTAMP());
  • Step 3: Check what is inserted in the table for the Location column.
1
2
3
4
SELECT
  *
FROM
  Employee;

2.3.3.5 Setting the MySQL DEFAULT value for the Location column.

As we have already set “London” as a DEFAULT value to Location column, we can see

Location = ”London” for the inserted record.

How to set DEFAULT values to a DateTime column in MySQL

  • Step 1: CREATE table with setting CURRENT_TIMESTAMP() as DEFAULT value to the Joining_Date column.
1
2
3
4
5
6
7
8
CREATE TABLE Employee (
  Employee_ID int NOT NULL,
  Last_Name varchar(255) NOT NULL,
  First_Name varchar(255),
  Age int,
  Location varchar(255),
  Joining_Date DATETIME DEFAULT CURRENT_TIMESTAMP()
);
  • Step 2: Insert a row in to table without a value for the Joining_Date column.
1
2
3
INSERT INTO
  employee(Employee_ID, Last_Name, First_Name, Age, Location)
VALUES(101, 'John', 'Wood', 35, 'London');
  • Step 3: Check what is inserted in the table for the Joining_Date column.
1
2
3
4
SELECT
  *
FROM
  Employee;

Setting the MySQL DEFAULT value for the Joining_Date column.

As we have already set CURRENT_TIMESTAMP() as the DEFAULT value for the Joining_Date column, we can see a value for Joining_Date. This value represents the CURRENT_TIMESTAMP ( or the TIMESTAMP when we inserted record in the table).

3. 常用SQL

3.1 按照时间统计

3.1.1 按月统计

1
2
3
4
5
6
7
select
  date_format(create_time, '%Y-%m') mont,
  count(*) coun
from
  t_content
group by
  date_format(create_time, '%Y-%m');

3.1.2 按天统计

1
2
3
4
5
6
7
select
  date_format(create_time, '%Y-%m-%d') dat,
  count(*) coun
from
  t_content
group by
  date_format(create_time, '%Y-%m-%d');
1
2
3
4
5
6
7
select
  from_unixtime(create_time / 1000, '%Y-%m-%d') dat,
  count(*) coun
from
  t_content
group by
  from_unixtime(create_time / 1000, '%Y-%m-%d')
1
2
3
4
5
6
7
8
9
10
11
SELECT
  date_format(e.gmt_create, '%Y-%m-%d') AS DAY,
  count(*) AS Count
FROM
  `live_right` e
WHERE
  date_format(e.gmt_create, '%Y-%m-%d') > '2020-10-29'
GROUP BY
  date_format(e.gmt_create, '%Y-%m-%d')
ORDER BY
  date_format(e.gmt_create, '%Y-%m-%d');

3.1.3 时间格式化

1
2
3
4
select
  from_unixtime(create_time / 1000, '%Y-%m-%d %H:%i:%S') create_time
from
  t_content

3.2 substring_index的用法

MySQL中一个很好用的截取字符串的函数：substring_index。

3.2.1 用法规则

substring_index（“待截取有用部分的字符串”，“截取数据依据的字符”，截取字符的位置N）

3.2.2 详细说明

首先，设待处理对象字符串为“15,151,152,16”（虽然这里指的不是iP，可以看作是IP来处理吧）

  • 这里截取的依据是逗号：“，”

  • 具体要截取第N个逗号前部分的字符;

    意思是：在字符串中以逗号为索引，获取不同索引位的字符。

3.2.2.1 举例

  • 取第一个逗号前的字符串 ：

root@localhost|iris>SELECT SUBSTRING_INDEX(‘15,151,152,16’,’,’,1);

+————————————————————+

| SUBSTRING_INDEX(‘15,151,152,16’,’,’,1) |

+————————————————————+

| 15 |

+————————————————————+

1 row in set (0.00 sec)

==>得到结果为： 15

  • 截取第二个逗号前面部分

root@localhost|iris>SELECT SUBSTRING_INDEX(‘15,151,152,16’,’,’,2);

+————————————————————+

| SUBSTRING_INDEX(‘15,151,152,16’,’,’,2) |

+————————————————————+

| 15,151 |

+————————————————————+

1 row in set (0.00 sec)

==>得到结果为：15,151

N可以为负数，表示倒数第N个索引字符后面的字符串。有负号的时候，可以将整个字符倒过来看，依旧是第N个字符前面的部分。

  • 截取目标字符串中最后一个含 “，” 位子的后的部分：

root@localhost|iris>SELECT SUBSTRING_INDEX(‘15,151,152,16’,’,’,-1) as ‘subrting’;

+—————+

| subrting |

+—————+

| 16 |

+—————+

1 row in set (0.00 sec)

==>得到结果为： 16

  • 取倒数第2个逗号前那部分字符串里，最后逗号后面的部分

root@localhost|iris>SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(‘15,151,152,16’,’,’,2),’,’,-1) as ‘subrting’;

+—————+

| subrting |

+—————+

| 151 |

+—————+

1 row in set (0.00 sec)

==>得到结果为： 151

  • 取倒数第二个逗号后面部分字符串，再去这部分里第一个都号前的部分：

root@localhost|iris>SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(‘15,151,152,16’,’,’,-2),’,’,1) as ‘subrting’;

+—————+

| subrting |

+—————+

| 152 |

+—————+

1 row in set (0.00 sec)

==> 得到结果为：152

原文链接：https://blog.csdn.net/iris_xuting/article/details/38415181

4. 经验总结

4.1 解决逻辑删除与数据库唯一约束冲突

4.1.1 前言

不知道大家有没有遇到这么一种业务场景，在业务中有个唯一约束A，当该业务进行逻辑删除后（设置标记为删除状态），再往唯一约束列插入相同的值时，此时会报Duplicate entry，但在业务上，该值时必须要插入的。今天我们就来聊聊处理这种业务场景的几种思路

4.1.2 解决思路

编号 方案 备注
1 不采用逻辑删除，直接物理删除
2 新建历史表 主表进行物理删除，同时将删除的记录保存到历史表中
3 取消表的唯一约束，同时引入redis来保证唯一约束 取消表的唯一约束，在项目中引入redis，通过redis来判重，新增时往redis set记录，删除时，删除redis记录
4 变更删除标记为时间戳 将删除状态不以0,1表示，而是以时间戳为值，然后将删除状态为与之前的唯一约束A重新组成唯一联合约束index（A、del_flag），删除时变更del_flag的时间戳
5 保留删除标记，同时新建一个字段del_unique_key 保留删除状态位，再新增一个字段del_unique_key,该字段默认值为0，字段类型和大小与主键id保持一致，同时与原先的唯一约束重新组成联合唯一约束index(A,del_unique_key),业务进行逻辑删除，变更del_unique_key的值为该删除行的主键id

4.1.3 方案的取舍

方案一得从业务的角度上考虑了，如果物理删除，对业务无损，那就无所谓了。方案二等于需要删除的记录的表都需要有历史表，如果仅仅是用来实现记录删除记录，感觉有点大材小用。方案三引入redis，虽然也可以解决问题，但是又额外增加复杂度，同时还得保证redis和数据库的一致性。方案四和方案五其实实现的思路是一样，不过如果已经是在线上跑的业务，还是推荐用第五种方案，毕竟新增字段正常对已有的业务影响相对较小，如果是第四种方案，直接将标志位修改为时间戳，可能还会涉及改业务。如果是新增业务，第四种和第五种方案比较推荐

4.2 事务

**铁律：对代码中的事务是否生效必须加以验证！**

4.2.1 开启事务

Spring使用@Transactional注解，需要首先添加启动注解。

1
2
3
4
5
6
7
8
9
@SpringBootApplication(exclude={DataSourceAutoConfiguration.class})
@EnableTransactionManagement
public class ApplicationLoader {
    public static void main(String[] args) {
        SpringApplication application = new SpringApplication(ApplicationLoader.class);
        application.run(args);
        System.out.println("Application startup success.");
    }
}

4.2.1 事务使用错误和失败场景

编号 场景 示例
1 不要在接口上声明@Transactional ，而要在具体类的方法上使用 @Transactional 注解，否则注解可能无效。
2 不要图省事，将@Transactional放置在类级的声明中。放在类声明，会使得所有方法都有事务。故@Transactional应该放在方法级别，不需要使用事务的方法，就不要放置事务，比如查询方法。否则对性能是有影响的。
3 使用了@Transactional的方法，对同一个类里面的方法调用， @Transactional无效。比如有一个类Test，它的一个方法A，A再调用Test本类的方法B（不管B是否public还是private），但A没有声明注解事务，而B有。则外部调用A之后，B的事务是不会起作用的。原因是在同一个类之中，方法互相调用，切面无效 ，而不仅仅是事务。这里事务之所以无效，是因为spring的事务是通过aop实现的。 Spring在扫描Bean的时候会自动为标注了@Transactional注解的类生成一个代理类（proxy）,当有注解的方法被调用的时候，实际上是代理类调用的，代理类在调用之前会开启事务，执行事务的操作，但是同类中的方法互相调用，相当于this.B()，此时的B方法并非是代理类调用，而是直接通过原有的Bean直接调用，所以注解会失效。
4 使用了@Transactional的方法，只能是public，@Transactional注解的方法都是被外部其他类调用才有效，故只能是public。道理和上面的有关联。故在protected、private 或者 package-visible 的方法上使用 @Transactional 注解，它也不会报错，但事务无效。 @Transactional注解使用的是AOP，在使用动态代理的时候只能针对public方法进行代理，源码依据在AbstractFallbackTransactionAttributeSource类中的computeTransactionAttribute方法中，如下：
5 如果在加有事务的方法内，使用了try...catch..语句块对异常进行了捕获，而catch语句块没有throw new RuntimeExecption异常，事务也不会回滚。
-------------本文结束感谢您的阅读-------------
我知道是不会有人点的，但万一有人想不开呢？