This repository has been archived by the owner on Sep 19, 2019. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 155
/
blog.sql
166 lines (136 loc) · 90.6 KB
/
blog.sql
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
-- phpMyAdmin SQL Dump
-- version phpStudy 2014
-- http://www.phpmyadmin.net
--
-- 主机: localhost
-- 生成日期: 2018 年 03 月 13 日 09:55
-- 服务器版本: 5.5.53
-- PHP 版本: 5.4.45
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- 数据库: `blog`
--
-- --------------------------------------------------------
--
-- 表的结构 `admin`
--
CREATE TABLE IF NOT EXISTS `admin` (
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`username` varchar(30) NOT NULL,
`password` char(32) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1206227 ;
--
-- 转存表中的数据 `admin`
--
INSERT INTO `admin` (`id`, `username`, `password`) VALUES
(10080, '雨云天下', '111111');
-- --------------------------------------------------------
--
-- 表的结构 `admin_login_log`
--
CREATE TABLE IF NOT EXISTS `admin_login_log` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '日志ID',
`admin_id` int(11) NOT NULL COMMENT '管理员ID',
`date` timestamp NULL DEFAULT NULL COMMENT '登陆日期',
`ip` varchar(30) DEFAULT NULL COMMENT '登陆IP',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=41 ;
--
-- 转存表中的数据 `admin_login_log`
--
INSERT INTO `admin_login_log` (`id`, `admin_id`, `date`, `ip`) VALUES
(25, 10080, '2018-02-27 03:39:48', '0:0:0:0:0:0:0:1'),
(26, 10080, '2018-02-27 05:34:29', '0:0:0:0:0:0:0:1'),
(27, 10080, '2018-02-27 06:41:30', '0:0:0:0:0:0:0:1'),
(28, 10080, '2018-02-27 07:18:22', '0:0:0:0:0:0:0:1'),
(29, 10080, '2018-02-27 07:18:46', '0:0:0:0:0:0:0:1'),
(30, 10080, '2018-02-27 07:28:14', '0:0:0:0:0:0:0:1'),
(31, 10080, '2018-02-27 07:32:13', '0:0:0:0:0:0:0:1'),
(32, 10080, '2018-02-27 07:51:58', '0:0:0:0:0:0:0:1'),
(33, 10080, '2018-02-27 08:11:26', '0:0:0:0:0:0:0:1'),
(34, 10080, '2018-02-27 08:21:32', '0:0:0:0:0:0:0:1'),
(35, 10080, '2018-02-27 08:28:39', '0:0:0:0:0:0:0:1'),
(36, 10080, '2018-02-27 08:43:33', '0:0:0:0:0:0:0:1'),
(37, 10080, '2018-02-27 09:14:37', '0:0:0:0:0:0:0:1'),
(38, 10080, '2018-02-27 09:40:24', '0:0:0:0:0:0:0:1'),
(39, 10080, '2018-02-27 09:56:18', '0:0:0:0:0:0:0:1'),
(40, 10080, '2018-03-13 09:37:35', '0:0:0:0:0:0:0:1');
-- --------------------------------------------------------
--
-- 表的结构 `article`
--
CREATE TABLE IF NOT EXISTS `article` (
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`title` varchar(50) NOT NULL COMMENT '标题',
`keywords` varchar(150) NOT NULL COMMENT '关键字',
`desci` varchar(500) NOT NULL COMMENT '描述',
`pic` varchar(500) DEFAULT NULL COMMENT '图片地址',
`content` text NOT NULL COMMENT '内容',
`click` mediumint(9) NOT NULL DEFAULT '0' COMMENT '点击量',
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '发表日期',
`catalog_id` mediumint(9) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=105 ;
--
-- 转存表中的数据 `article`
--
INSERT INTO `article` (`id`, `title`, `keywords`, `desci`, `pic`, `content`, `click`, `time`, `catalog_id`) VALUES
(98, 'http的请求与响应-----content-type', '', 'content-type 指请求消息头的中请求消息数据的格式\r\n\r\n有三种用法。', NULL, '<p style="margin: 10px auto;">content-type 指请求消息头的中请求消息数据的格式<br/><br/>有三种用法<br/><br/>第一种：设置在request header的参数中<br/><br/>js中可以在发送请求前在请求消息头中设置content-type<br/>var xhr = window.XMLHttpRequest ? new window.XMLHttpRequest() : new ActiveXObject('Microsoft.XMLHTTP');<br/>xhr.open(method, url, true);<br/>xhr.onreadystatechange = function () {};<br/>xhr.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded');<br/>xhr.send(params);<br/><br/>第二种：设置在发送请求页面的header中<br/><header><br/> <meta content="text/html" charset="utf-8"/><br/></header><br/>第三种：设置在form表单提交的enctype参数中<br/><form enctype="multipart/form-data"></form><br/><br/>格式 Content-Type: type/subtype;parameter;<br/>例如 Content-Type: text/html;charset:utf-8;<br/> <br/>type:text,或者application,或者*<br/> text-------主类型是文本类型<br/> application----主类型是应用<br/> *--------------所有类型都适用<br/> <br/>subtype:子类型<br/> *---------------所有类型都适用<br/> html------------子类型为html格式<br/> xml-------------子类型为xml<br/> png-------------子类型为png格式<br/> <br/>parameter：参数，常用的是编码方式参数charset:utf-8<br/><br/>type/subtype 即是互联网媒体类型，也叫作MIME-Type<br/><br/>常见的媒体类型<br/><br/>主类型是text的<br/><br/> text/html ： HTML格式<br/> text/plain ：纯文本格式 <br/> text/xml ： XML格式<br/> image/png： png图片格式<br/> <br/>主类型是application的<br/><br/> application/xhtml+xml ：XHTML格式<br/> application/xml ： XML数据格式<br/> application/json ： JSON数据格式<br/> application/octet-stream ： 二进制流数据（如常见的文件下载）<br/><br/>特殊的用于上传文件时的mime-type<br/> multipart/form-data ： 需要在表单中进行文件上传时，就需要使用该格式<br/> application/x-www-form-urlencoded: 表单提交默认的mime-type,设置在enctype参数中，表单数据将会以k1=v1&k1=v1&k1=v1的形式发送到服务器<br/> <br/>mime-type的设置与文件类型，文件后缀名密切相关，如tomcat中的web.xml文件中就有详尽的mime-mappping设置<br/>详细的mime-type列表：http://www.w3school.com.cn/media/media_mimeref.asp</p><p>标签: <a href="http://www.cnblogs.com/carolddz/tag/web%E5%89%8D%E7%AB%AF/" style="color: rgb(159, 130, 72); text-decoration-line: none;">web前端</a>, <a href="http://www.cnblogs.com/carolddz/tag/http%E8%AF%B7%E6%B1%82/" style="color: rgb(159, 130, 72); text-decoration-line: none;">http请求</a>, <a href="http://www.cnblogs.com/carolddz/tag/%E5%AA%92%E4%BD%93%E7%B1%BB%E5%9E%8B/" style="color: rgb(159, 130, 72); text-decoration-line: none;">媒体类型</a>, <a href="http://www.cnblogs.com/carolddz/tag/content-type/" style="color: rgb(159, 130, 72); text-decoration-line: none;">content-type</a></p><p><br/></p>', 90, '2018-02-27 05:36:46', 0),
(99, '快速定位生产故障问题-JVM进程CPU占用率高于100%', '', '这几年作为技术leader处理过的线上紧急问题，掐指一算应该有不下10次吧（说多了都是泪啊~~）。所以挡刀救火也是leader的必备技能！本文主要分享遇到“JVM进程CPU占用率超100%”时如何快速定位原因。', NULL, '<h1 style="margin: 8px 0px 16px; padding: 0px; box-sizing: border-box; font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; line-height: 36px; color: rgb(79, 79, 79); font-size: 28px; white-space: normal; background-color: rgb(255, 255, 255);">前言</h1><blockquote style="box-sizing: border-box; margin: 0px 0px 24px; padding: 16px; border-left: 8px solid rgb(221, 223, 228); background: rgb(238, 240, 244); border-radius: 0px 5px 5px 0px; overflow: auto; word-wrap: normal; word-break: normal; color: rgb(63, 63, 63); font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; white-space: normal;"><p style="margin-top: 0px; margin-bottom: 0px; padding: 0px; box-sizing: border-box; font-size: 14px; color: rgb(153, 153, 153); line-height: 22px; text-align: justify; word-wrap: break-word; word-break: normal;">古语有云：人在江湖漂，哪有不挨刀。</p></blockquote><p style="margin-top: 0px; margin-bottom: 16px; padding: 0px; box-sizing: border-box; color: rgb(79, 79, 79); line-height: 26px; text-align: justify; word-wrap: break-word; word-break: normal; font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; white-space: normal; background-color: rgb(255, 255, 255);">这几年作为技术leader处理过的线上紧急问题，掐指一算应该有不下10次吧（<span style="box-sizing: border-box; font-weight: 700;">说多了都是泪啊~~</span>）。所以挡刀救火也是leader的必备技能！本文主要分享遇到“<span style="box-sizing: border-box; font-weight: 700;">JVM进程CPU占用率超100%</span>”时如何快速定位原因。</p><h1 style="margin: 8px 0px 16px; padding: 0px; box-sizing: border-box; font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; line-height: 36px; color: rgb(79, 79, 79); font-size: 28px; white-space: normal; background-color: rgb(255, 255, 255);"><a target="_blank" style="box-sizing: border-box; color: rgb(12, 137, 207);"></a>1. 生产故障-JVM进程CPU占用率高于100%</h1><p style="margin-top: 0px; margin-bottom: 16px; padding: 0px; box-sizing: border-box; color: rgb(79, 79, 79); line-height: 26px; text-align: justify; word-wrap: break-word; word-break: normal; font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; white-space: normal; background-color: rgb(255, 255, 255);">某日，运维同学反馈生产环境有故障，某个JVM进程CPU负载一直居高不下。登入服务器用 top -c 命令查看如下：</p><pre class="prettyprint" style="white-space: nowrap; word-wrap: break-word; box-sizing: border-box; position: relative; overflow-y: hidden; overflow-x: auto; margin-top: 0px; margin-bottom: 24px; font-family: Consolas, Inconsolata, Courier, monospace; padding: 8px 16px 4px 56px; font-size: 14px; line-height: 22px; word-break: break-all; background-color: rgb(246, 248, 250); border: none; border-radius: 0px;">top - 00:37:39 up 48 days, 10:41, 5 users, load average: 3.34, 3.18, 3.10Tasks: 166 total, 1 running, 165 sleeping, 0 stopped, 0 zombie\r\nCpu0 : 0.7%us, 0.3%sy, 0.0%ni, 99.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%stCpu1 :100.0%us, 0.0%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%stCpu2 : 1.3%us, 0.3%sy, 0.0%ni, 98.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%stCpu3 : 0.3%us, 0.0%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%stMem: 16332280k total, 15744336k used, 587944k free, 200632k buffers\r\nSwap: 8191992k total, 408724k used, 7783268k free, 7201204k cached\r\n\r\n PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND \r\n 9702 bb 20 0 2898m 348m 14m S 100.6 2.2 01:47.18 java -server -Xms512M -Xmx512M -Xmn192M -Xss256k -XX:PermSize=64M -XX:MaxPermSize=128M -verbose:\r\n ...123456789101112</pre><p style="margin-top: 0px; margin-bottom: 16px; padding: 0px; box-sizing: border-box; color: rgb(79, 79, 79); line-height: 26px; text-align: justify; word-wrap: break-word; word-break: normal; font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; white-space: normal; background-color: rgb(255, 255, 255);">从输出结果看，CPU load 达到3以上，服务器是4核机器，负载已经不低了。进程ID为9720的进程CPU占用率超过100%。</p><h1 style="margin: 8px 0px 16px; padding: 0px; box-sizing: border-box; font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; line-height: 36px; color: rgb(79, 79, 79); font-size: 28px; white-space: normal; background-color: rgb(255, 255, 255);"><a target="_blank" style="box-sizing: border-box; color: rgb(12, 137, 207);"></a>2. 定位故障的思路</h1><ul style="box-sizing: border-box; margin-bottom: 24px; padding: 0px; color: rgb(63, 63, 63); font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; white-space: normal; background-color: rgb(255, 255, 255);" class=" list-paddingleft-2"><li><p>首先找出问题进程内CPU占用率高的线程</p></li><li><p>再通过线程栈信息找出该线程当时在运行的问题代码段</p></li></ul><h1 style="margin: 8px 0px 16px; padding: 0px; box-sizing: border-box; font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; line-height: 36px; color: rgb(79, 79, 79); font-size: 28px; white-space: normal; background-color: rgb(255, 255, 255);"><a target="_blank" style="box-sizing: border-box; color: rgb(12, 137, 207);"></a>3. 定位故障的步骤</h1><h2 style="margin: 8px 0px 16px; padding: 0px; box-sizing: border-box; font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; line-height: 32px; color: rgb(79, 79, 79); font-size: 24px; white-space: normal; background-color: rgb(255, 255, 255);"><a target="_blank" style="box-sizing: border-box; color: rgb(12, 137, 207);"></a>3.1 用top命令查找进程内导致CPU占用率高的线程</h2><p style="margin-top: 0px; margin-bottom: 16px; padding: 0px; box-sizing: border-box; color: rgb(79, 79, 79); line-height: 26px; text-align: justify; word-wrap: break-word; word-break: normal; font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; white-space: normal; background-color: rgb(255, 255, 255);">先祭出第一招， top命令加上 -H -b 两个选项：-H 可以查看由某个进程启动的所有线程，-b 选项指定以批处理模式输出结果 。具体命令如下：</p><pre class="prettyprint" style="white-space: nowrap; word-wrap: break-word; box-sizing: border-box; position: relative; overflow-y: hidden; overflow-x: auto; margin-top: 0px; margin-bottom: 24px; font-family: Consolas, Inconsolata, Courier, monospace; padding: 8px 16px 4px 56px; font-size: 14px; line-height: 22px; word-break: break-all; background-color: rgb(246, 248, 250); border: none; border-radius: 0px;">$ top -Hbp 9702top - 00:38:54 up 48 days, 10:43, 5 users, load average: 0.81, 0.36, 0.16Tasks: 38 total, 1 running, 37 sleeping, 0 stopped, 0 zombieCpu(s): 3.5%us, 1.4%sy, 0.0%ni, 94.5%id, 0.3%wa, 0.0%hi, 0.2%si, 0.0%stMem: 16332280k total, 15744500k used, 587780k free, 200632k buffersSwap: 8191992k total, 408724k used, 7783268k free, 7201392k cached\r\n\r\n PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 10007 bb 20 0 2898m 348m 14m R 100.5 2.2 1:41.31 java \r\n 9702 bb 20 0 2898m 348m 14m S 0.0 2.2 0:00.00 java \r\n 9705 bb 20 0 2898m 348m 14m S 0.0 2.2 0:06.44 java \r\n 9706 bb 20 0 2898m 348m 14m S 0.0 2.2 0:00.11 java \r\n 9707 bb 20 0 2898m 348m 14m S 0.0 2.2 0:00.11 java \r\n 9708 bb 20 0 2898m 348m 14m S 0.0 2.2 0:00.11 java \r\n 9709 bb 20 0 2898m 348m 14m S 0.0 2.2 0:00.10 java \r\n 9710 bb 20 0 2898m 348m 14m S 0.0 2.2 0:00.00 java \r\n 9711 bb 20 0 2898m 348m 14m S 0.0 2.2 0:00.07 java \r\n 9712 bb 20 0 2898m 348m 14m S 0.0 2.2 0:00.03 java \r\n 9713 bb 20 0 2898m 348m 14m S 0.0 2.2 0:00.03 java \r\n 9714 bb 20 0 2898m 348m 14m S 0.0 2.2 0:00.00 java \r\n 9715 bb 20 0 2898m 348m 14m S 0.0 2.2 0:00.00 java \r\n 9716 bb 20 0 2898m 348m 14m S 0.0 2.2 0:05.60 java12345678910111213141516171819202122</pre><p style="margin-top: 0px; margin-bottom: 16px; padding: 0px; box-sizing: border-box; color: rgb(79, 79, 79); line-height: 26px; text-align: justify; word-wrap: break-word; word-break: normal; font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; white-space: normal; background-color: rgb(255, 255, 255);">输出结果里的PID其实是线程ID，可以看到线程ID为10007的线程CPU占用率为100.5%，它就是罪魁祸首。上面的命令可以再优化下：查出CPU占用率超过某个值的所有线程，例如超过50%，如下：</p><pre class="prettyprint" style="white-space: nowrap; word-wrap: break-word; box-sizing: border-box; position: relative; overflow-y: hidden; overflow-x: auto; margin-top: 0px; margin-bottom: 24px; font-family: Consolas, Inconsolata, Courier, monospace; padding: 8px 16px 4px 56px; font-size: 14px; line-height: 22px; word-break: break-all; background-color: rgb(246, 248, 250); border: none; border-radius: 0px;">top -Hbp 9702 | awk '/java/ && $9>50'1</pre><h2 style="margin: 8px 0px 16px; padding: 0px; box-sizing: border-box; font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; line-height: 32px; color: rgb(79, 79, 79); font-size: 24px; white-space: normal; background-color: rgb(255, 255, 255);"><a target="_blank" style="box-sizing: border-box; color: rgb(12, 137, 207);"></a>3.2 用JVM命令 jstack 精确定位出问题的代码段</h2><p style="margin-top: 0px; margin-bottom: 16px; padding: 0px; box-sizing: border-box; color: rgb(79, 79, 79); line-height: 26px; text-align: justify; word-wrap: break-word; word-break: normal; font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; white-space: normal; background-color: rgb(255, 255, 255);">第二招，用jstack 命令dump出线程栈信息，可以直接定位到出问题的代码段。这里也分两步。第一步把上面的线程ID <span style="box-sizing: border-box; font-weight: 700;">10007</span> 转化为十六进制：</p><pre class="prettyprint" style="white-space: nowrap; word-wrap: break-word; box-sizing: border-box; position: relative; overflow-y: hidden; overflow-x: auto; margin-top: 0px; margin-bottom: 24px; font-family: Consolas, Inconsolata, Courier, monospace; padding: 8px 16px 4px 56px; font-size: 14px; line-height: 22px; word-break: break-all; background-color: rgb(246, 248, 250); border: none; border-radius: 0px;">$ printf "%x\\n" 10007271712</pre><p style="margin-top: 0px; margin-bottom: 16px; padding: 0px; box-sizing: border-box; color: rgb(79, 79, 79); line-height: 26px; text-align: justify; word-wrap: break-word; word-break: normal; font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; white-space: normal; background-color: rgb(255, 255, 255);">因为jstack输出的线程栈信息中，线程ID是以十六进制展示的。</p><p style="margin-top: 0px; margin-bottom: 16px; padding: 0px; box-sizing: border-box; color: rgb(79, 79, 79); line-height: 26px; text-align: justify; word-wrap: break-word; word-break: normal; font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; white-space: normal; background-color: rgb(255, 255, 255);">第二步使用命令 jstack $pid | grep “线程id” 把信息打印出来：</p><pre class="prettyprint" style="white-space: nowrap; word-wrap: break-word; box-sizing: border-box; position: relative; overflow-y: hidden; overflow-x: auto; margin-top: 0px; margin-bottom: 24px; font-family: Consolas, Inconsolata, Courier, monospace; padding: 8px 16px 4px 56px; font-size: 14px; line-height: 22px; word-break: break-all; background-color: rgb(246, 248, 250); border: none; border-radius: 0px;">$ jstack 9702 | grep '2717' -A 30"http-nio-9092-exec-1" daemon prio=10 tid=0x00007f3a90014800 nid=0x2717 runnable [0x00007f3afc72a000]\r\n java.lang.Thread.State: RUNNABLE at com.bb.apigateway.filter.pre.SignatureTokenFilter.run(SignatureTokenFilter.java:44) at com.netflix.zuul.ZuulFilter.runFilter(ZuulFilter.java:112) at com.netflix.zuul.FilterProcessor.processZuulFilter(FilterProcessor.java:197) at com.netflix.zuul.FilterProcessor.runFilters(FilterProcessor.java:161) at com.netflix.zuul.FilterProcessor.preRoute(FilterProcessor.java:136) at com.netflix.zuul.ZuulRunner.preRoute(ZuulRunner.java:105) at com.netflix.zuul.http.ZuulServlet.preRoute(ZuulServlet.java:125) at com.netflix.zuul.http.ZuulServlet.service(ZuulServlet.java:74) at org.springframework.web.servlet.mvc.ServletWrappingController.handleRequestInternal(ServletWrappingController.java:158) at org.springframework.cloud.netflix.zuul.web.ZuulController.handleRequestInternal(ZuulController.java:43) at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:147) at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:50) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:961) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:895) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:967) at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:869) at javax.servlet.http.HttpServlet.service(HttpServlet.java:648) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:843) at javax.servlet.http.HttpServlet.service(HttpServlet.java:729) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207) at org.springframework.boot.actuate.autoconfigure.EndpointWebMvcAutoConfiguration$ApplicationContextHeaderFilter.doFilterInternal(EndpointWebMvcAutoConfiguration.java:261) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207) at org.springframework.boot.actuate.trace.WebRequestTraceFilter.doFilterInternal(WebRequestTraceFilter.java:115)1234567891011121314151617181920212223242526272829303132</pre><p style="margin-top: 0px; margin-bottom: 16px; padding: 0px; box-sizing: border-box; color: rgb(79, 79, 79); line-height: 26px; text-align: justify; word-wrap: break-word; word-break: normal; font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; white-space: normal; background-color: rgb(255, 255, 255);">下面这行就是出问题的业务代码，根据实际情况修复BUG即可</p><blockquote style="box-sizing: border-box; margin: 0px 0px 24px; padding: 16px; border-left: 8px solid rgb(221, 223, 228); background: rgb(238, 240, 244); border-radius: 0px 5px 5px 0px; overflow: auto; word-wrap: normal; word-break: normal; color: rgb(63, 63, 63); font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; white-space: normal;"><p style="margin-top: 0px; margin-bottom: 0px; padding: 0px; box-sizing: border-box; font-size: 14px; color: rgb(153, 153, 153); line-height: 22px; text-align: justify; word-wrap: break-word; word-break: normal;">com.bb.apigateway.filter.pre.SignatureTokenFilter.run(SignatureTokenFilter.java:44)</p></blockquote><h1 style="margin: 8px 0px 16px; padding: 0px; box-sizing: border-box; font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; line-height: 36px; color: rgb(79, 79, 79); font-size: 28px; white-space: normal; background-color: rgb(255, 255, 255);"><a target="_blank" style="box-sizing: border-box; color: rgb(12, 137, 207);"></a>4. 小结</h1><p style="margin-top: 0px; margin-bottom: 16px; padding: 0px; box-sizing: border-box; color: rgb(79, 79, 79); line-height: 26px; text-align: justify; word-wrap: break-word; word-break: normal; font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; white-space: normal; background-color: rgb(255, 255, 255);">思路：</p><ul style="box-sizing: border-box; margin-bottom: 24px; padding: 0px; color: rgb(63, 63, 63); font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; white-space: normal; background-color: rgb(255, 255, 255);" class=" list-paddingleft-2"><li><p>找出CPU占用率高的进程</p></li><li><p>找出问题进程内CPU占用率高的线程</p></li><li><p>通过线程栈信息找出该线程当时在运行的问题代码段</p></li></ul><p style="margin-top: 0px; margin-bottom: 16px; padding: 0px; box-sizing: border-box; color: rgb(79, 79, 79); line-height: 26px; text-align: justify; word-wrap: break-word; word-break: normal; font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; white-space: normal; background-color: rgb(255, 255, 255);">实施要点：</p><ul style="box-sizing: border-box; margin-bottom: 24px; padding: 0px; color: rgb(63, 63, 63); font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; white-space: normal; background-color: rgb(255, 255, 255);" class=" list-paddingleft-2"><li><p>top -Hbp ‘pid’ 定位问题线程</p></li><li><p>jstack ‘pid’ | grep ‘thread_id’ 找出问题代码</p></li></ul><p><br/></p>', 85, '2018-02-27 05:55:10', 0);
INSERT INTO `article` (`id`, `title`, `keywords`, `desci`, `pic`, `content`, `click`, `time`, `catalog_id`) VALUES
(100, '12 条用于 Linux 的 MySQL/MariaDB 安全最佳实践', '', 'MySQL 是世界上最流行的开源数据库系统，MariaDB(一个 MySQL 分支)是世界上增长最快的开源数据库系统。在安装 MySQL 服务器之后，在默认配置下是不安全的，确保数据库安全通常是通用数据库管理的基本任务之一。\r\n\r\n这将有助于增', NULL, '<p style="margin-top: 0px; margin-bottom: 16px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(79, 79, 79); line-height: 26px; min-height: 26px; text-align: justify; font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; white-space: normal; background-color: rgb(255, 255, 255);"><img src="http://ss.csdn.net/p?http://mmbiz.qpic.cn/mmbiz_png/R3InYSAIZkFicgXkSPOxuFYtXnKlybxW6vHXwdnIlp6UBvhXniaV5D1Hqiaib3ShXIcwba0Mmibm6mKxjJkbe5nrhFA/?wx_fmt=png&wxfrom=5&wx_lazy=1" alt="?wx_fmt=png&wxfrom=5&wx_lazy=1"/></p><p style="margin-top: 0px; margin-bottom: 16px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(79, 79, 79); line-height: 26px; min-height: 26px; text-align: justify; font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; white-space: normal; background-color: rgb(255, 255, 255);"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); font-family: Avenir, -apple-system-font, 微软雅黑, sans-serif; white-space: pre-line;">MySQL 是世界上最流行的开源数据库系统，MariaDB(一个 MySQL 分支)是世界上增长最快的开源数据库系统。在安装 MySQL 服务器之后，在默认配置下是不安全的，确保数据库安全通常是通用数据库管理的基本任务之一。</span></p><p style="margin-top: 15px; margin-bottom: 15px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); line-height: 30px; min-height: 26px; text-align: justify; background-color: rgb(255, 255, 255); white-space: pre-line; font-family: Avenir, -apple-system-font, 微软雅黑, sans-serif;">这将有助于增强和提升整个 Linux 服务器的安全性，因为攻击者总是扫描系统任意部分的漏洞，而数据库在过去是重点目标区域。一个常见的例子是对 MySQL 数据库的 root 密码的强制破解。</p><p style="margin-top: 15px; margin-bottom: 15px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); line-height: 30px; min-height: 26px; text-align: justify; background-color: rgb(255, 255, 255); white-space: pre-line; font-family: Avenir, -apple-system-font, 微软雅黑, sans-serif;">在本指南中，我们将会讲解对开发者有帮助的 MySQL/MariaDB 的 Linux 最佳安全实践。</p><h2 style="font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; margin: 8px 0px 16px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(21, 153, 87); font-size: 24px; line-height: 1.35; white-space: normal; background-color: rgb(255, 255, 255);"><a style="color: rgb(78, 161, 219); outline: 0px; margin: 0px; padding: 0px; font-weight: 400; box-sizing: border-box;"></a>1. 安全地安装 MySQL</h2><p style="margin-top: 15px; margin-bottom: 15px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); line-height: 30px; min-height: 26px; text-align: justify; background-color: rgb(255, 255, 255); white-space: pre-line; font-family: Avenir, -apple-system-font, 微软雅黑, sans-serif;">这是安装 MySQL 服务器后第一个建议的步骤，用于保护数据库服务器。这个脚本可以帮助您提高 MySQL 服务器的安全性：</p><ul class=" list-paddingleft-2" style="list-style-type: square;"><li><p style="margin-top: 0px; margin-bottom: 16px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(79, 79, 79); line-height: 26px; min-height: 26px; text-align: justify;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); line-height: 22px; font-size: 14px !important;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; line-height: 22px;">如果您在安装期间没有设置 root 帐户的密码，马上设置它</span></span></p></li><li><p style="margin-top: 0px; margin-bottom: 16px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(79, 79, 79); line-height: 26px; min-height: 26px; text-align: justify;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); line-height: 22px; font-size: 14px !important;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; line-height: 22px;">通过删除可从本地主机外部访问的 root 帐户来禁用远程 root 用户登录</span></span></p></li><li><p style="margin-top: 0px; margin-bottom: 16px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(79, 79, 79); line-height: 26px; min-height: 26px; text-align: justify;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); line-height: 22px; font-size: 14px !important;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; line-height: 22px;">删除匿名用户帐户和测试数据库，默认情况下，所有用户、甚至匿名用户都可以访问这些帐户和测试数据库</span></span></p></li></ul><pre class="prettyprint linenums prettyprinted" style="margin-top: 0px; margin-bottom: 0px; padding: 8px 0px 6px; box-sizing: border-box; position: relative; overflow-y: hidden; overflow-x: auto; outline: 0px; border: none; background-color: rgb(241, 239, 238); font-family: Consolas, Inconsolata, Courier, monospace; font-size: 10px; line-height: 12px; color: rgb(80, 97, 109);"><br/></pre><ol class="linenums list-paddingleft-2" style="list-style-type: none;"><li><p style="margin-top: 0px; margin-bottom: 16px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(79, 79, 79); line-height: 26px; min-height: 26px; text-align: justify;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); display: block; line-height: 22px; font-size: 14px !important;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; line-height: 22px; display: block;"><code style="box-sizing: border-box; outline: 0px; font-family: inherit !important; font-size: 10px; line-height: 12px; color: rgb(199, 37, 78); background-color: rgb(249, 242, 244); border-radius: 4px; margin-left: -20px; border-width: 0px; white-space: pre !important;"><span class="com" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(156, 148, 145); outline: 0px; line-height: 20px; font-size: 13px !important;"># mysql_secure_installation</span></code></span></span></p></li></ol><p style="margin-top: 15px; margin-bottom: 15px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); line-height: 30px; min-height: 26px; text-align: justify; background-color: rgb(255, 255, 255); white-space: pre-line; font-family: Avenir, -apple-system-font, 微软雅黑, sans-serif;">在运行上述命令之后，设置 root 密码并通过输入 [Yes/Y] 和按下 [Enter] 键来回答一系列问题。</p><p style="margin-top: 15px; margin-bottom: 15px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); line-height: 30px; min-height: 26px; text-align: justify; background-color: rgb(255, 255, 255); white-space: pre-line; font-family: Avenir, -apple-system-font, 微软雅黑, sans-serif;"><img class="img_loading" src="http://ss.csdn.net/p?http://mmbiz.qpic.cn/mmbiz_png/R3InYSAIZkFicgXkSPOxuFYtXnKlybxW6E7VBdolLsBl4DiaPMtlKiaVGaoqelAsjeWOrm7zPG581oOJhVDQ6dh6A/?wx_fmt=png" alt="?wx_fmt=png"/></p><p style="margin-top: 15px; margin-bottom: 15px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); line-height: 30px; min-height: 26px; text-align: justify; background-color: rgb(255, 255, 255); white-space: pre-line; font-family: Avenir, -apple-system-font, 微软雅黑, sans-serif;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px;">安全安装 MySQL 情况界面</span></p><h2 style="font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; margin: 8px 0px 16px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(21, 153, 87); font-size: 24px; line-height: 1.35; white-space: normal; background-color: rgb(255, 255, 255);"><a style="color: rgb(78, 161, 219); outline: 0px; margin: 0px; padding: 0px; font-weight: 400; box-sizing: border-box;"></a>2. 将数据库服务器绑定到 Loopback 地址</h2><p style="margin-top: 15px; margin-bottom: 15px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); line-height: 30px; min-height: 26px; text-align: justify; background-color: rgb(255, 255, 255); white-space: pre-line; font-family: Avenir, -apple-system-font, 微软雅黑, sans-serif;">此配置将限制来自远程机器的访问，它告诉 MySQL 服务器只接受来自本地主机的连接。你可以在主配置文件中进行设置。</p><pre class="prettyprint linenums prettyprinted" style="margin-top: 0px; margin-bottom: 0px; padding: 8px 0px 6px; box-sizing: border-box; position: relative; overflow-y: hidden; overflow-x: auto; outline: 0px; border: none; background-color: rgb(241, 239, 238); font-family: Consolas, Inconsolata, Courier, monospace; font-size: 10px; line-height: 12px; color: rgb(80, 97, 109);"><br/></pre><ol class="linenums list-paddingleft-2" style="list-style-type: none;"><li><p style="margin-top: 0px; margin-bottom: 16px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(79, 79, 79); line-height: 26px; min-height: 26px; text-align: justify;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); display: block; line-height: 22px; font-size: 14px !important;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; line-height: 22px; display: block;"><code style="box-sizing: border-box; outline: 0px; font-family: inherit !important; font-size: 10px; line-height: 12px; color: rgb(199, 37, 78); background-color: rgb(249, 242, 244); border-radius: 4px; margin-left: -20px; border-width: 0px; white-space: pre !important;"><span class="com" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(156, 148, 145); outline: 0px; line-height: 20px; font-size: 13px !important;"># vi /etc/my.cnf [RHEL/CentOS] </span></code></span></span></p></li><li><p style="margin-top: 0px; margin-bottom: 16px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(79, 79, 79); line-height: 26px; min-height: 26px; text-align: justify;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); display: block; line-height: 22px; font-size: 14px !important;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; line-height: 22px; display: block;"><code style="box-sizing: border-box; outline: 0px; font-family: inherit !important; font-size: 10px; line-height: 12px; color: rgb(199, 37, 78); background-color: rgb(249, 242, 244); border-radius: 4px; margin-left: -20px; border-width: 0px; white-space: pre !important;"><span class="com" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(156, 148, 145); outline: 0px; line-height: 20px; font-size: 13px !important;"># vi /etc/mysql/my.conf [Debian/Ubuntu] </span></code></span></span></p></li><li><p style="margin-top: 0px; margin-bottom: 16px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(79, 79, 79); line-height: 26px; min-height: 26px; text-align: justify;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); display: block; line-height: 22px; font-size: 14px !important;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; line-height: 22px; display: block;"><code style="box-sizing: border-box; outline: 0px; font-family: inherit !important; font-size: 10px; line-height: 12px; color: rgb(199, 37, 78); background-color: rgb(249, 242, 244); border-radius: 4px; margin-left: -20px; border-width: 0px; white-space: pre !important;"><span class="pln" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(27, 25, 24); outline: 0px; line-height: 20px; font-size: 13px !important;">OR</span></code></span></span></p></li><li><p style="margin-top: 0px; margin-bottom: 16px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(79, 79, 79); line-height: 26px; min-height: 26px; text-align: justify;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); display: block; line-height: 22px; font-size: 14px !important;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; line-height: 22px; display: block;"><code style="box-sizing: border-box; outline: 0px; font-family: inherit !important; font-size: 10px; line-height: 12px; color: rgb(199, 37, 78); background-color: rgb(249, 242, 244); border-radius: 4px; margin-left: -20px; border-width: 0px; white-space: pre !important;"><span class="com" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(156, 148, 145); outline: 0px; line-height: 20px; font-size: 13px !important;"># vi /etc/mysql/mysql.conf.d/mysqld.cnf [Debian/Ubuntu]</span></code></span></span></p></li></ol><p style="margin-top: 15px; margin-bottom: 15px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); line-height: 30px; min-height: 26px; text-align: justify; background-color: rgb(255, 255, 255); white-space: pre-line; font-family: Avenir, -apple-system-font, 微软雅黑, sans-serif;">在 <code class="prettyprint code-in-text prettyprinted" style="position: relative; overflow-y: hidden; overflow-x: auto; box-sizing: border-box; outline: 0px; font-family: Consolas, Inconsolata, Courier, monospace; font-size: 14px; line-height: 18px; color: rgb(88, 88, 88); background: rgb(243, 241, 241); border-radius: 4px; margin: 0px 0px 24px; padding: 8px 16px 4px 56px; border: none;"><span class="pun" style="margin: 0px; padding: 0px 2px; box-sizing: border-box; color: rgb(102, 102, 0); outline: 0px; display: inline-block;">[</span><span class="pln" style="margin: 0px; padding: 0px 2px; box-sizing: border-box; color: rgb(0, 0, 0); outline: 0px; display: inline-block;">mysqld</span><span class="pun" style="margin: 0px; padding: 0px 2px; box-sizing: border-box; color: rgb(102, 102, 0); outline: 0px; display: inline-block;">]</span></code> 部分中添加下面这一行</p><pre class="prettyprint linenums prettyprinted" style="margin-top: 0px; margin-bottom: 0px; padding: 8px 0px 6px; box-sizing: border-box; position: relative; overflow-y: hidden; overflow-x: auto; outline: 0px; border: none; background-color: rgb(241, 239, 238); font-family: Consolas, Inconsolata, Courier, monospace; font-size: 10px; line-height: 12px; color: rgb(80, 97, 109);"><br/></pre><ol class="linenums list-paddingleft-2" style="list-style-type: none;"><li><p style="margin-top: 0px; margin-bottom: 16px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(79, 79, 79); line-height: 26px; min-height: 26px; text-align: justify;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); display: block; line-height: 22px; font-size: 14px !important;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; line-height: 22px; display: block;"><code style="box-sizing: border-box; outline: 0px; font-family: inherit !important; font-size: 10px; line-height: 12px; color: rgb(199, 37, 78); background-color: rgb(249, 242, 244); border-radius: 4px; margin-left: -20px; border-width: 0px; white-space: pre !important;"><span class="pln" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(27, 25, 24); outline: 0px; line-height: 20px; font-size: 13px !important;">bind</span><span class="pun" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(27, 25, 24); outline: 0px; line-height: 20px; font-size: 13px !important;">-</span><span class="pln" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(27, 25, 24); outline: 0px; line-height: 20px; font-size: 13px !important;">address </span><span class="pun" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(27, 25, 24); outline: 0px; line-height: 20px; font-size: 13px !important;">=</span><span class="pln" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(27, 25, 24); outline: 0px; line-height: 20px; font-size: 13px !important;"> </span><span class="lit" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(223, 83, 32); outline: 0px; line-height: 20px; font-size: 13px !important;">127.0</span><span class="pun" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(27, 25, 24); outline: 0px; line-height: 20px; font-size: 13px !important;">.</span><span class="lit" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(223, 83, 32); outline: 0px; line-height: 20px; font-size: 13px !important;">0.1</span></code></span></span></p></li></ol><h2 style="font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; margin: 8px 0px 16px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(21, 153, 87); font-size: 24px; line-height: 1.35; white-space: normal; background-color: rgb(255, 255, 255);"><a style="color: rgb(78, 161, 219); outline: 0px; margin: 0px; padding: 0px; font-weight: 400; box-sizing: border-box;"></a>3. 禁用 MySQL 的 LOCAL INFILE</h2><p style="margin-top: 15px; margin-bottom: 15px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); line-height: 30px; min-height: 26px; text-align: justify; background-color: rgb(255, 255, 255); white-space: pre-line; font-family: Avenir, -apple-system-font, 微软雅黑, sans-serif;">作为安全性增强的一部分，您需要禁用 <span style="box-sizing: border-box; outline: 0px; font-weight: 700; color: rgb(0, 0, 0);">local_infile</span>，使用下面的指令以防止在 <code class="prettyprint code-in-text prettyprinted" style="position: relative; overflow-y: hidden; overflow-x: auto; box-sizing: border-box; outline: 0px; font-family: Consolas, Inconsolata, Courier, monospace; font-size: 14px; line-height: 18px; color: rgb(88, 88, 88); background: rgb(243, 241, 241); border-radius: 4px; margin: 0px 0px 24px; padding: 8px 16px 4px 56px; border: none;"><span class="pun" style="margin: 0px; padding: 0px 2px; box-sizing: border-box; color: rgb(102, 102, 0); outline: 0px; display: inline-block;">[</span><span class="pln" style="margin: 0px; padding: 0px 2px; box-sizing: border-box; color: rgb(0, 0, 0); outline: 0px; display: inline-block;">mysqld</span><span class="pun" style="margin: 0px; padding: 0px 2px; box-sizing: border-box; color: rgb(102, 102, 0); outline: 0px; display: inline-block;">]</span></code> 部分从 MySQL 中访问底层文件系统。</p><pre class="prettyprint linenums prettyprinted" style="margin-top: 0px; margin-bottom: 0px; padding: 8px 0px 6px; box-sizing: border-box; position: relative; overflow-y: hidden; overflow-x: auto; outline: 0px; border: none; background-color: rgb(241, 239, 238); font-family: Consolas, Inconsolata, Courier, monospace; font-size: 10px; line-height: 12px; color: rgb(80, 97, 109);"><br/></pre><ol class="linenums list-paddingleft-2" style="list-style-type: none;"><li><p style="margin-top: 0px; margin-bottom: 16px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(79, 79, 79); line-height: 26px; min-height: 26px; text-align: justify;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); display: block; line-height: 22px; font-size: 14px !important;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; line-height: 22px; display: block;"><code style="box-sizing: border-box; outline: 0px; font-family: inherit !important; font-size: 10px; line-height: 12px; color: rgb(199, 37, 78); background-color: rgb(249, 242, 244); border-radius: 4px; margin-left: -20px; border-width: 0px; white-space: pre !important;"><span class="kwd" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(102, 102, 234); outline: 0px; line-height: 20px; font-size: 13px !important;">local</span><span class="pun" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(27, 25, 24); outline: 0px; line-height: 20px; font-size: 13px !important;">-</span><span class="pln" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(27, 25, 24); outline: 0px; line-height: 20px; font-size: 13px !important;">infile</span><span class="pun" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(27, 25, 24); outline: 0px; line-height: 20px; font-size: 13px !important;">=</span><span class="lit" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(223, 83, 32); outline: 0px; line-height: 20px; font-size: 13px !important;">0</span></code></span></span></p></li></ol><h2 style="font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; margin: 8px 0px 16px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(21, 153, 87); font-size: 24px; line-height: 1.35; white-space: normal; background-color: rgb(255, 255, 255);"><a style="color: rgb(78, 161, 219); outline: 0px; margin: 0px; padding: 0px; font-weight: 400; box-sizing: border-box;"></a>4. 修改 MySQL 的默认端口</h2><p style="margin-top: 15px; margin-bottom: 15px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); line-height: 30px; min-height: 26px; text-align: justify; background-color: rgb(255, 255, 255); white-space: pre-line; font-family: Avenir, -apple-system-font, 微软雅黑, sans-serif;">设置端口变量用于监听 TCP/IP 连接的 MySQL 端口号。默认端口号是 3306，但是您可以在 <span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px;">*[mysqld] *</span>中修改它。</p><pre class="prettyprint linenums prettyprinted" style="margin-top: 0px; margin-bottom: 0px; padding: 8px 0px 6px; box-sizing: border-box; position: relative; overflow-y: hidden; overflow-x: auto; outline: 0px; border: none; background-color: rgb(241, 239, 238); font-family: Consolas, Inconsolata, Courier, monospace; font-size: 10px; line-height: 12px; color: rgb(80, 97, 109);"><br/></pre><ol class="linenums list-paddingleft-2" style="list-style-type: none;"><li><p style="margin-top: 0px; margin-bottom: 16px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(79, 79, 79); line-height: 26px; min-height: 26px; text-align: justify;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); display: block; line-height: 22px; font-size: 14px !important;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; line-height: 22px; display: block;"><code style="box-sizing: border-box; outline: 0px; font-family: inherit !important; font-size: 10px; line-height: 12px; color: rgb(199, 37, 78); background-color: rgb(249, 242, 244); border-radius: 4px; margin-left: -20px; border-width: 0px; white-space: pre !important;"><span class="typ" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(64, 126, 231); outline: 0px; line-height: 20px; font-size: 13px !important;">Port</span><span class="pun" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(27, 25, 24); outline: 0px; line-height: 20px; font-size: 13px !important;">=</span><span class="lit" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(223, 83, 32); outline: 0px; line-height: 20px; font-size: 13px !important;">5000</span></code></span></span></p></li></ol><h2 style="font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; margin: 8px 0px 16px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(21, 153, 87); font-size: 24px; line-height: 1.35; white-space: normal; background-color: rgb(255, 255, 255);"><a style="color: rgb(78, 161, 219); outline: 0px; margin: 0px; padding: 0px; font-weight: 400; box-sizing: border-box;"></a>5、启用 MySQL 日志</h2><p style="margin-top: 15px; margin-bottom: 15px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); line-height: 30px; min-height: 26px; text-align: justify; background-color: rgb(255, 255, 255); white-space: pre-line; font-family: Avenir, -apple-system-font, 微软雅黑, sans-serif;">日志是了解服务运行过程中发生了什么的最好的方法之一，在受到任何攻击的时候都可以很容易的从日志里看到任何入侵相关的行为。可以通过将下边的变量添加到配置文件[mysqld]部分来开启mysql日志功能。</p><pre class="prettyprint linenums prettyprinted" style="margin-top: 0px; margin-bottom: 0px; padding: 8px 0px 6px; box-sizing: border-box; position: relative; overflow-y: hidden; overflow-x: auto; outline: 0px; border: none; background-color: rgb(241, 239, 238); font-family: Consolas, Inconsolata, Courier, monospace; font-size: 10px; line-height: 12px; color: rgb(80, 97, 109);"><br/></pre><ol class="linenums list-paddingleft-2" style="list-style-type: none;"><li><p style="margin-top: 0px; margin-bottom: 16px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(79, 79, 79); line-height: 26px; min-height: 26px; text-align: justify;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); display: block; line-height: 22px; font-size: 14px !important;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; line-height: 22px; display: block;"><code style="box-sizing: border-box; outline: 0px; font-family: inherit !important; font-size: 10px; line-height: 12px; color: rgb(199, 37, 78); background-color: rgb(249, 242, 244); border-radius: 4px; margin-left: -20px; border-width: 0px; white-space: pre !important;"><span class="pln" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(27, 25, 24); outline: 0px; line-height: 20px; font-size: 13px !important;">log</span><span class="pun" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(27, 25, 24); outline: 0px; line-height: 20px; font-size: 13px !important;">=</span><span class="str" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(123, 151, 38); outline: 0px; line-height: 20px; font-size: 13px !important;">/var/</span><span class="pln" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(27, 25, 24); outline: 0px; line-height: 20px; font-size: 13px !important;">log</span><span class="pun" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(27, 25, 24); outline: 0px; line-height: 20px; font-size: 13px !important;">/</span><span class="pln" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(27, 25, 24); outline: 0px; line-height: 20px; font-size: 13px !important;">mysql</span><span class="pun" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(27, 25, 24); outline: 0px; line-height: 20px; font-size: 13px !important;">.</span><span class="pln" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(27, 25, 24); outline: 0px; line-height: 20px; font-size: 13px !important;">log</span></code></span></span></p></li></ol><h2 style="font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; margin: 8px 0px 16px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(21, 153, 87); font-size: 24px; line-height: 1.35; white-space: normal; background-color: rgb(255, 255, 255);"><a style="color: rgb(78, 161, 219); outline: 0px; margin: 0px; padding: 0px; font-weight: 400; box-sizing: border-box;"></a>6、设置合适的 MySQL 文件的访问权限</h2><p style="margin-top: 15px; margin-bottom: 15px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); line-height: 30px; min-height: 26px; text-align: justify; background-color: rgb(255, 255, 255); white-space: pre-line; font-family: Avenir, -apple-system-font, 微软雅黑, sans-serif;">确保你已经为所有的 mysql 服务文件和数据路径设置了合适的访问权限。文件 /etc/my.conf 只能由 root 用户修改，这样就可以阻止其他用户修改数据库服务的配置。</p><pre class="prettyprint linenums prettyprinted" style="margin-top: 0px; margin-bottom: 0px; padding: 8px 0px 6px; box-sizing: border-box; position: relative; overflow-y: hidden; overflow-x: auto; outline: 0px; border: none; background-color: rgb(241, 239, 238); font-family: Consolas, Inconsolata, Courier, monospace; font-size: 10px; line-height: 12px; color: rgb(80, 97, 109);"><br/></pre><ol class="linenums list-paddingleft-2" style="list-style-type: none;"><li><p style="margin-top: 0px; margin-bottom: 16px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(79, 79, 79); line-height: 26px; min-height: 26px; text-align: justify;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); display: block; line-height: 22px; font-size: 14px !important;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; line-height: 22px; display: block;"><code style="box-sizing: border-box; outline: 0px; font-family: inherit !important; font-size: 10px; line-height: 12px; color: rgb(199, 37, 78); background-color: rgb(249, 242, 244); border-radius: 4px; margin-left: -20px; border-width: 0px; white-space: pre !important;"><span class="com" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(156, 148, 145); outline: 0px; line-height: 20px; font-size: 13px !important;"># chmod 644 /etc/my.cnf</span></code></span></span></p></li></ol><h2 style="font-family: "PingFang SC", "Microsoft YaHei", SimHei, Arial, SimSun; margin: 8px 0px 16px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(21, 153, 87); font-size: 24px; line-height: 1.35; white-space: normal; background-color: rgb(255, 255, 255);"><a style="color: rgb(78, 161, 219); outline: 0px; margin: 0px; padding: 0px; font-weight: 400; box-sizing: border-box;"></a>7、删除 MySQL shell 历史</h2><p style="margin-top: 15px; margin-bottom: 15px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); line-height: 30px; min-height: 26px; text-align: justify; background-color: rgb(255, 255, 255); white-space: pre-line; font-family: Avenir, -apple-system-font, 微软雅黑, sans-serif;">你在 MySQL shell 中执行的所有的命令都会被 mysql 客户端保存到一个历史文件：~/.mysql_history。这样是很危险的，因为对于你创建过的任何用户账户，所有的在 shell 输入过的用户名和密码都会记录到历史文件里面。</p><pre class="prettyprint linenums prettyprinted" style="margin-top: 0px; margin-bottom: 0px; padding: 8px 0px 6px; box-sizing: border-box; position: relative; overflow-y: hidden; overflow-x: auto; outline: 0px; border: none; background-color: rgb(241, 239, 238); font-family: Consolas, Inconsolata, Courier, monospace; font-size: 10px; line-height: 12px; color: rgb(80, 97, 109);"><br/></pre><ol class="linenums list-paddingleft-2" style="list-style-type: none;"><li><p style="margin-top: 0px; margin-bottom: 16px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(79, 79, 79); line-height: 26px; min-height: 26px; text-align: justify;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; color: rgb(74, 74, 74); display: block; line-height: 22px; font-size: 14px !important;"><span style="margin: 0px; padding: 0px; box-sizing: border-box; outline: 0px; line-height: 22px; display: block;"><code style="box-sizing: border-box; outline: 0px; font-family: inherit !important; font-size: 10px; line-height: 12px; color: rgb(199, 37, 78); background-color: rgb(249, 242, 244); border-radius: 4px; margin-left: -20px; border-width: 0px; white-space: pre !important;"><span class="com" style="margin: 0px; padding: 0px; box-sizing: border-box; color: rgb(156, 148, 145); outline: 0px; line-height: 20px; font-size: 13px !important;"># cat /dev/null > ~/.mysql_history</span></code></span></span></p></li></ol><p><br/></p>', 2, '2018-02-27 08:57:29', 0),
(101, 'MySQL数据库视图：视图定义、创建视图、修改视图', '', 'MySQL数据库视图：视图定义、创建视图、修改视图MySQL数据库视图：视图定义、创建视图、修改视图', NULL, '<blockquote><p>视图是指计算机数据库中的视图，是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。——百度百科</p></blockquote><p>关系型数据库中的数据是由一张一张的二维关系表所组成，简单的单表查询只需要遍历一个表，而复杂的多表查询需要将多个表连接起来进行查询任务。对于复杂的查询事件，每次查询都需要编写MySQL代码效率低下。为了解决这个问题，数据库提供了视图（view）功能。</p><h2 id="0-视图相关的mysql指令"><a name="t0" target="_blank"></a><strong>0 视图相关的MySQL指令</strong></h2><table><thead><tr><th>操作指令</th><th>代码</th></tr></thead><tbody><tr><td>创建视图</td><td><code>CREATE VIEW 视图名(列1，列2...) AS SELECT (列1，列2...) FROM ...;</code></td></tr><tr><td>使用视图</td><td><code>当成表使用就好</code></td></tr><tr><td>修改视图</td><td><code>CREATE OR REPLACE VIEW 视图名 AS SELECT [...] FROM [...];</code></td></tr><tr><td>查看数据库已有视图</td><td><code>>SHOW TABLES [like...];</code>（可以使用模糊查找）</td></tr><tr><td>查看视图详情</td><td><code>DESC 视图名</code>或者<code>SHOW FIELDS FROM 视图名</code></td></tr><tr><td>视图条件限制</td><td><code>[WITH CHECK OPTION]</code></td></tr></tbody></table><h2 id="1-视图"><a name="t1" target="_blank"></a><strong>1 视图</strong></h2><p>百度百科定义了什么是视图，但是对缺乏相关知识的人可能还是难以理解或者只有一个比较抽象的概念，笔者举个例子来解释下什么是视图。</p><p><em>朕想要了解皇宫的国库的相关情况，想知道酒窖有什么酒，剩多少，窖藏多少年，于是派最信任的高公公去清点，高公公去国库清点后报给了朕；朕又想知道藏书情况，于是又派高公公去清点并回来报告给朕，又想知道金银珠宝如何，又派高公公清点。。。过一段时间又想知道藏书情况，高公公还得重新再去清点，皇上问一次，高公公就得跑一次路。</em></p><p><em>后来皇上觉得高公公不容易，就成立了国库管理部门，小邓子负责酒窖，小卓子负责藏书，而小六子负责金库的清点。。。后来皇上每次想了解国库就直接问话负责人，负责人就按照职责要求进行汇报。</em> <br><img src="http://img.blog.csdn.net/20170318153958211?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvbW94aWdhbmRhc2h1/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast" alt="视图" title=""></p><p>安排专人管理后，每次皇上想要了解国库情况，就不必让高公公每次都跑一趟，而是指定的人员按照指定的任务完成指定的汇报工作就可以了。</p><p>和数据库相对应，每次进行查询工作，都需要编写查询代码进行查询</p>', 4, '2018-02-27 09:34:51', 0),
(102, 'Mysql ——基础篇', '', 'Mysql ——基础篇Mysql ——基础篇', NULL, '<p><img src="http://img.t.sinajs.cn/t4/appstyle/expression/ext/normal/0b/tootha_org.gif" alt="[嘻嘻]" data-w-e="1"><br></p><div><div>原创 2018年02月26日 16:58:26</div></div><div id="article_content"><div><p><font size="2" color="celadon">mysql 核心技术手册 第二版</font> <br><em><font size="2">阅读此博文假设你具备一些mysql基础知识，或者是已阅读过上一篇博文：<a href="http://blog.csdn.net/qq_37049781/article/details/79368517" target="_blank">mysql的安装与配置-（快速入门）</a></font></em></p><h2 id="创建数据库和表"><a name="t0"></a>创建数据库和表</h2><ul><li><strong>为一个虚拟的书店建立一个数据库</strong></li></ul><pre><code>CREATE DATABASE bookstore;</code><ul><li>1</li></ul></pre><p><code>!</code>通过上述语句就创建了一个 bookstore 的数据库。 <br><font size="1" color="red"><em>Mysql 语句对保留字的大小写并不敏感，对数据库与表名的大小写是否敏感取决于系统。最好以保留字大写，表名等小写的形式进行书写。</em></font></p><ul><li><strong>切换数据库，查看当前数据库</strong></li></ul><pre><code>USE bookstore;\r\nSELECT DATABASE();</code><ul><li>1</li><li>2</li></ul></pre><ul><li><strong>创建一个存放图书基本信息的数据表</strong></li></ul><pre><code>CREATE TABLE books(\r\nbook_id INT,\r\ntitle VARCHAR (50),\r\nauthor vARCHAR (50)\r\n)</code><ul><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li></ul></pre><p></p><ul><br><li>查看表结构 <br></li></ul><p></p><blockquote><p>对于接触一个表来说，这个命令可以让你更好</p></blockquote></div></div>', 6, '2018-02-27 09:36:19', 0),
(103, 'spring data jpa 查询自定义字段，转换为自定义实体', '', '目标：查询数据库中的字段，然后转换成 JSON 格式的数据，返回前台。', NULL, '<p><strong>环境：idea 2016.3.4， jdk 1.8， mysql 5.6， spring-boot 1.5.2</strong> <br><strong>背景：首先建立 entity 映射数据库（非专业 java 不知道这怎么说）</strong></p><pre><code>@Entity\r\n@Table(name = "user")\r\npublic class User {\r\n @Id\r\n @GeneratedValue(strategy = GenerationType.AUTO)\r\n private Long id;\r\n private String userName; // 账号\r\n private String password; // 密码\r\n // getter setter 方法略过\r\n}</code><ul><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li><li>9</li><li>10</li></ul></pre><p>然后建立与之对应的 model</p><pre><code>public class UserModel implements Serializable {\r\n // 一些属性\r\n}</code><ul><li>1</li><li>2</li><li>3</li></ul></pre><p>这里我们分情况讨论</p><h3 id="首先第一种情况查询的字段与表中的字段全部对应就是查表里所有的字段但是使用-model-作为接收对象"><a name="t1"></a>首先第一种情况：查询的字段与表中的字段全部对应（就是查表里所有的字段，但是使用 Model 作为接收对象）</h3><p>这种情况比较简单，调用 Repository 提供的方法，返回一个 entity , 然后将 entity 的属性复制到 model 中。像这样</p><pre><code>UserModel user = new UserModel();\r\nUser userEntity = new User();\r\n// 一个工具类，具体使用方法请百度\r\nBeanUtils.copyProperties(user, userEntity);</code><ul><li>1</li><li>2</li><li>3</li><li>4</li></ul></pre><h3 id="第二种情况只查询指定的几个字段"><a name="t2"></a>第二种情况：只查询指定的几个字段</h3><p>现在我有张表，有字段如下：</p><pre><code>@Entity\r\n@Table(name = "user_info")\r\npublic class UserInfo {\r\n @Id\r\n @GeneratedValue(strategy = GenerationType.AUTO)\r\n private Long id;\r\n private String name = "用户"; // 昵称\r\n private String signature; // 个性签名\r\n private String gender = "未知"; // 性别\r\n private String description; // 个人说明\r\n private String avatar; // 头像\r\n private Long role; // 权限\r\n private Boolean disable; // 是否冻结\r\n private Date createTime; // 创建时间\r\n private Boolean isDelete; // 是否删除\r\n private Long userId; // 用户 Id\r\n // ...\r\n}</code><ul><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li><li>9</li><li>10</li><li>11</li><li>12</li><li>13</li><li>14</li><li>15</li><li>16</li><li>17</li><li>18</li></ul></pre><p>但是我只需要查询指定的几个字段，然后转换成 JSON，返回给前台，咋办呢？</p><h4 id="第一种方法使用-model-查询时转化">第一种方法：使用 model 查询时转化</h4><p>首先建立一个 model ，写上自己想要查询的字段，然后写上构造函数，这步很重要，因为spring jpa 转化时会调用这个构造方法</p><pre><code>public class MyModel implements Serializable {\r\n\r\n private String userName;\r\n private String name;\r\n private String gender;\r\n private String description;\r\n\r\n public MyModel() {};\r\n\r\n public MyModel(String userName, String name, String gender, String description) {\r\n this.userName = userName;\r\n this.name = name;\r\n this.gender = gender;\r\n this.description = description;\r\n }\r\n}</code><ul><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li><li>9</li><li>10</li><li>11</li><li>12</li><li>13</li><li>14</li><li>15</li><li>16</li></ul></pre><p>然后在 dao 类中写查询方法</p><pre><code>@Query(value = "select new pers.zhuch.model.MyModel(u.userName, ui.name, ui.gender, ui.description) from UserInfo ui, User u where u.id = ui.userId")\r\npublic List<MyModel> getAllRecord();</code><ul><li>1</li><li>2</li></ul></pre><p>直接在查询语句中 new model 框架底层会调用它，然后返回这个对象（这里我写了完整的类路径，不写的时候它报错说找不到类型什么的）</p><p>然后就可以获得只有指定字段的 model 了。然后就把它转成 JSON 格式就 O 了。</p><h4 id="第二种方法在service-里边转换成-json">第二种方法：在service 里边转换成 JSON</h4><p>原理其实和第一种方法差不多，只是处理结果的方式不太一样，只是这种方法我们就不在 hql 中 new Model 了，直接写查询方法</p><pre><code>@Query(value = "select new map(u.userName, ui.name, ui.gender, ui.description) from UserInfo ui, User u where u.id = ui.userId")\r\npublic List<Map<String, Object>> getCustomField();</code><ul><li>1</li><li>2</li></ul></pre><p>直接new map（这里得是小写，不知道大写有木有问题，反正没试，编译器提示是要小写的） <br>然后返回的结果是这样的</p><pre><code>[\r\n {\r\n "0": "admin", \r\n "1": "你猜", \r\n "2": "男", \r\n "3": "一段描述"\r\n }, {\r\n "0": "abc", \r\n "1": "你猜人家", \r\n "2": "女", \r\n "3": "没事先挂了"\r\n }\r\n]</code><ul><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li><li>9</li><li>10</li><li>11</li><li>12</li><li>13</li></ul></pre><p>然后在 service 层里直接封装成 JSON 对象，返回</p><pre><code>List<JsonObject> list = new ArrayList();\r\nfor(Map map : result) {\r\n JsonObject j = new JsonObject();\r\n j.addProperty(attrName, val);\r\n ...\r\n list.add(j);\r\n}\r\ngson.toJson(list);</code><ul><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li></ul></pre><p>还有一种返回结果，这样写：</p><pre><code>@Query(value = "select u.userName, ui.name, ui.gender, ui.description from UserInfo ui, User u where u.id = ui.userId")\r\npublic List<Object> getCustomField();</code><ul><li>1</li><li>2</li></ul></pre><p>返回结果是这样的格式：</p><pre><code>[\r\n [\r\n "admin", \r\n "你猜", \r\n "男", \r\n "一段描述"\r\n ], [\r\n "abc", \r\n "你猜人家", \r\n "女", \r\n "没事先挂了"\r\n ]\r\n]</code><ul><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li><li>9</li><li>10</li><li>11</li><li>12</li><li>13</li></ul></pre><p>返回的是数组，也一样可以通过上面的方法转成 json ，这里我的程序中出现了一点点 BUG，就是空值的字段不会在数组中，不知道为什么。</p><p>这种方法必须明确的知道查询了哪些字段，灵活性比较差，虽然它解决了手头的问题。还有就是版本的不同，有可能会出现丢失空字段的情况，我个人特别的不喜欢这样的方法，万一我实体几十个字段，写着写着忘了写到哪了，就 over 了</p><h4 id="第三种方法返回一个便于转换成-json-格式的-list">第三种方法：返回一个便于转换成 json 格式的 list</h4><p>其实和上面很相似，都是 dao 层返回一个 List < Map < String, Object >>，但是上面的结果集返回的 Map 的 key 只是列的下标，这种方式稍微理想一点点，就是 Map 的 key 就是查询的列名。但是这种方式需要实现自定义 Repository( 这里不详细介绍，请自行百度 )，并且只是 jpa 集成 hibenate 的时候可以使用。</p><pre><code>public List getCustomEntity() {\r\n String sql = "select t.id, t.name, t.gender, t.is_delete, t.create_time, t.description from t_entity t";\r\n Query query = em.createNativeQuery(sql);\r\n // Query 接口是 spring-data-jpa 的接口，而 SQLQuery 接口是 hibenate 的接口，这里的做法就是先转成 hibenate 的查询接口对象，然后设置结果转换器\r\n query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);\r\n return query.getResultList();\r\n}</code><ul><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li></ul></pre><p>这种方法返回的就是比较标准的 JSON 格式的 java 对象了，只需要用 jackson 或者 Gson 转一下就是标准的 json 了</p><pre><code>[\r\n {\r\n attr: val,\r\n ...\r\n },\r\n {\r\n attr: val,\r\n ...\r\n },\r\n]</code><ul><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li><li>9</li><li>10</li></ul></pre><p>这种方式其实已经比较理想了，因为直接就能返回到前台，但是有时候，结果不是一条 sql 能够解决的，得两条或者以上的 sql 来解决一个复杂的查询需求，这个过程中，结果比较需要转换成 pojo，以便于组装操作。</p><h4 id="第四种方案dao-中直接转成-pojo-返回">第四种方案：dao 中直接转成 pojo 返回</h4><p>这个方案还是依赖于 hibenate，有点操蛋，但是更明确一些。</p><pre><code>public List getCustomEntity() {\r\n String sql = "select t.id, t.name, t.gender, t.is_delete as isEnable, t.create_time as createTime, t.description from t_entity t";\r\n Query query = em.createNativeQuery(sql);\r\n query.unwrap(SQLQuery.class)\r\n // 这里是设置字段的数据类型，有几点注意，首先这里的字段名要和目标实体的字段名相同，然后 sql 语句中的名称（别名）得与实体的相同\r\n .addScalar("id", StandardBasicTypes.LONG)\r\n .addScalar("name", StandardBasicTypes.STRING)\r\n .addScalar("gender", StandardBasicTypes.STRING)\r\n .addScalar("isEnable", StandardBasicTypes.BOOLEAN)\r\n .addScalar("createTime", StandardBasicTypes.STRING)\r\n .addScalar("description", StandardBasicTypes.STRING)\r\n .setResultTransformer(Transformers.aliasToBean(EntityModel.class));\r\n return query.getResultList();\r\n}</code><ul><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li><li>9</li><li>10</li><li>11</li><li>12</li><li>13</li><li>14</li></ul></pre><p>这次返回的就是 List 了。这里要注意的是 StandardBasicTypes这个常量类，在一些旧版本中，是 Hibenate 类，具体哪个包我不知道，我这个版本中是换成了前面的那个常量类</p>', 11, '2018-02-27 09:41:53', 0);
INSERT INTO `article` (`id`, `title`, `keywords`, `desci`, `pic`, `content`, `click`, `time`, `catalog_id`) VALUES
(104, 'Spring Boot 文件上传原理', 'Spring Boot', ' 首先我们要知道什么是Spring Boot，这里简单说一下，Spring Boot可以看作是一个框架中的框架--->集成了各种框架，像security、jpa、data、cloud等等，它无须关心配置可以快速启动开发，有兴趣可以了解下自动化配置实现原理,本质上是 spring 4.0的条件化配置实现，深抛下注解，就会看到了。\r\n\r\n 说Spring Boot 文件上传原理 其实就是Spring MVC，因为这部分工作是Spring MVC做的而不是Spring Boot，那么，SpringMVC又是怎么处理文件上传这个过程的呢？', NULL, '<div id="cnblogs_post_body"><p>首先我们要知道什么是<b>Spring Boot</b><img src="http://img.t.sinajs.cn/t4/appstyle/expression/ext/normal/3c/pcmoren_wu_org.png" alt="[污]" data-w-e="1" style="font-size: 1rem;"><span style="font-size: 1rem;">，这里简单说一下，Spring Boot可以看作是一个框架中的框架--->集成了各种框架，像security、jpa、data、cloud等等，它无须关心配置可以快速启动开发，有兴趣可以了解下自动化配置实现原理,本质上是 spring 4.0的条件化配置实现，深抛下注解，</span><span style="font-size: 1rem;">就会看到了。</span></p><p> 说Spring Boot 文件上传原理 其实就是Spring MVC，因为这部分工作是Spring MVC做的而不是Spring Boot，那么，SpringMVC又是怎么处理文件上传这个过程的呢？</p><p> 图:</p><p><img src="https://images2018.cnblogs.com/blog/737414/201803/737414-20180313150430223-69937817.png" alt=""></p><p> 首先项目启动加载相关配置，再执行上述第二步的时候 DispatcherServlet会去查找id为multipartResolver的Bean，在配置中看到Bean指向的是CommonsMultipartResolve，其中实现了MultipartResolver接口。</p><p> 第四步骤这里会判断是否multipart文件即isMultipart方法，返回true：就会调用 multipartResolver 方法，传递HttpServletRequest会返回一个MultipartHttpServletRequest对象，再由DispatcherServlet进行处理到Controller层；返回false：会忽略掉，继续传递HttpServletRequest。</p><p> 在MVC中需要在配置文件webApplicationContext.xml中配置 如下：</p><div><pre> <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">\r\n <property name="defaultEncoding" value="UTF-8"/>\r\n <property name="maxUploadSize" value="100000000"/>\r\n <property name="uploadTempDir" value="fileUpload/temp"/>\r\n </bean></pre></div><p> 而Spring Boot已经自动配置好，直接用就行，做个test没什么问题，有默认的上传限制大小(maxFileSize = "1MB",maxRequestSize = "10MB",fileSizeThreshold = "0")，不过在实际开发中我们还是要做一些配置的，</p><p>如下在application.properties中：</p><div><div><br></div><pre># multipart config<br>#默认支持文件上传\r\nspring.http.multipart.enabled=true<br>#文件上传目录\r\nspring.http.multipart.location=/tmp/file/images/<br>#将文件写入磁盘的阈值\r\nspring.http.multipart.file-size-threshold=5MB<br>#最大支持请求大小\r\nspring.http.multipart.max-request-size=20MB</pre></div><p>当然也可以写配置类来实现，具体的就不做展示了。</p><p> 看完上述你肯定有个大概的了解了，这里再啰嗦下，Spring提供Multipart的解析器：MultipartResolver，上述说的是<strong>CommonsMultipartResolver，</strong>它是基于Commons File Upload第三方来实现，这也是在Servlet3.0之前的东西，3.0+之后也可以不需要依赖第三方库，可以用StandardServletMultipartResolver，同样也是实现了MultipartResolver接口，我们可以看下它的实现源码(上有注释，不再进行解析了)：</p><p>这里是之前写的test后者实现配置类，可以简单看下，作为了解：</p><div><br></div></div><div id="MySignature">业精于勤荒于嬉。</div>', 6, '2018-03-13 09:52:42', 0);
-- --------------------------------------------------------
--
-- 表的结构 `catalog`
--
CREATE TABLE IF NOT EXISTS `catalog` (
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL COMMENT '栏目名称',
`keywords` varchar(150) NOT NULL COMMENT '栏目关键词',
`desc` text NOT NULL COMMENT '栏目描述',
`type` tinyint(1) NOT NULL DEFAULT '0' COMMENT '栏目类型 0：列表；1：留言',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
--
-- 转存表中的数据 `catalog`
--
INSERT INTO `catalog` (`id`, `name`, `keywords`, `desc`, `type`) VALUES
(1, 'PHP', 'PHP', 'PHP', 0),
(2, '数据库', '数据库', '数据库', 0),
(3, 'Java', 'Java', 'Java', 0),
(4, '生活感悟', '生活感悟', '生活感悟', 0);
-- --------------------------------------------------------
--
-- 表的结构 `comment`
--
CREATE TABLE IF NOT EXISTS `comment` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '评论id',
`article_id` bigint(20) NOT NULL COMMENT '文章id',
`content` text NOT NULL COMMENT '评论内容',
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '评论日期',
`name` varchar(10) DEFAULT NULL COMMENT '留言者昵称',
`email` varchar(30) DEFAULT NULL COMMENT '留言者邮箱',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=12 ;
--
-- 转存表中的数据 `comment`
--
INSERT INTO `comment` (`id`, `article_id`, `content`, `date`, `name`, `email`) VALUES
(1, 93, '写的不错。', '2017-09-16 08:10:48', '追梦人', NULL),
(9, 91, '留言测试', '2018-02-27 03:27:00', '雨云天下', '[email protected]'),
(11, 104, '测试', '2018-03-13 09:54:00', 'skiiy', '[email protected]');
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;