平时记录在工作中，会把随手查到的内容，记在文件里面，时间一久，比较零乱，文件太长，在里面查找也不方便。于是想到随便整理一下存数据库得了。
先创建数据库，mysql8 支持全文索引，自带分词器，用起来很方便。
CREATE TABLE `books` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `title` (`title`),
FULLTEXT KEY `content` (`title`,`content`) WITH PARSER `ngram`
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
然后就是保存数据，并通过全文索引搜索即可。
创建全文索引
CREATE FULLTEXT INDEX idx_name ON tablename(title,content) WITH PARSER NGRAM;
查询语句
select * from books where match(title,content) against('北京日报' IN NATURAL LANGUAGE MODE);
select * from books where match(title,content) against('北京日报 -青鸟' IN BOOLEAN MODE);
查询有两种模式，一个是自然模式，一个布尔模式，布尔模式就是支持+- 等符号更精准的条件。
nginx 配置，支持 php 的 pathinfo
server {
listen 80 default_server;
listen [::]:80 default_server;
server_name loc.tool.com;
root /mnt/d/dev/php/tools;
charset utf-8;
location / {
index index.php index.html;
}
error_page 404 /404.html;
location = /40x.html {
}
error_page 500 502 503 504 /50x.html;
location = /50x.html {
}
location ~ \.php(.*)$ {
fastcgi_pass unix:/run/php/fpm.sock;
fastcgi_index index.php;
fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
fastcgi_param PATH_INFO $1;
include fastcgi_params;
}
}
下面是主要代码，用到一个mysql 工具类，是以前自己封装的，比较简陋。当时的想法是定义的查询方法和原生的一样，select(field)->from(table)->where(where)->limit() 像这样就没有学习成本了。
require './MysqlTool.php';
$db = new MysqlTool();
$db->connect(db:'test');
$table = 'books';
$pathinfo = trim($_SERVER['PATH_INFO'], '/');
$records = [];
if(!empty($pathinfo)){
if($pathinfo == 'add'){
echo $db->insert($table, ['title'=> $_POST[0], 'content' => $_POST[1]]) > 0 ? '添加成功' : '添加失败';
exit(0);
}else{
$keyword = $_POST['search'];
$records = $db->select('title,content')->from($table)->where("MATCH(title, content) AGAINST ('$keyword' IN BOOLEAN MODE)")->limit();
}
}
?>
$article) { ?>
class MysqlTool
{
protected $sql = [];
protected $error = '';
protected $connect = null;
protected $sth = null;
public function connect($host = '127.0.0.1', $user='test', $password='123456', $db = 'test', $charset='utf8')
{
$this->connect = new PDO("mysql:dbname=$db;host=$host", $user, $password);
$this->connect->setAttribute(PDO::ATTR_CASE, PDO::CASE_NATURAL);
$this->connect->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$this->connect->query("SET NAMES $charset;");
return $this;
}
public function beginTransaction()
{
$this->connect->beginTransaction();
}
public function commit()
{
$this->connect->commit();
}
public function rollBack()
{
$this->connect->rollBack();
}
public function query($sql='')
{
$this->sql($sql);
return $this->connect->query($sql)->fetchAll();
}
public function select($field='*')
{
$this->options['select'] = 'SELECT ' . $field;
return $this;
}
public function from($table)
{
$this->options['from'] = ' FROM `'. str_replace('.', '`.`', $table) .'`';
return $this;
}
public function where($where, $param=null)
{
$this->options['where'] = ' WHERE '. $where;
if (!empty($param)) {
$this->options['param'] = $param;
}
return $this;
}
public function order($order)
{
$this->options['order'] = ' ORDER BY '. $order;
return $this;
}
public function limit($start=0, $length=null)
{
if ($length == null) {
if ($start > 0) {
$length = $start;
$start = 0;
}
}
if ($length > 0) {
$this->options['limit'] = ' LIMIT '. $start .','. $length;
}
$options = array('select', 'from', 'join', 'where', 'order', 'limit');
$sql = '';
foreach ($options as $keyword) {
if (isset($this->options[$keyword])) {
$sql .= $this->options[$keyword];
}
}
if (isset($this->options['param'])) {
$this->sth = $this->connect->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$this->sth->execute($this->options['param']);
$query = $this->sth;
} else {
$query = $this->connect->query($sql);
}
$this->sql($sql);
unset($this->options);
return ($length == 1) ? $query->fetch() : $query->fetchAll();
}
public function insert($table, $data)
{
if (isset($data['id'])) {
unset($data['id']);
}
$fields = array_keys($data);
$values = array_values($data);
$sql = 'INSERT INTO `'. $table .'`(`' .implode('`,`', $fields). '`)VALUES("' .implode('","', $values).'")';
$this->sql($sql);
return $this->connect->exec($sql);
}
public function update($table, $data)
{
$sql = 'UPDATE `'. $table .'` SET ';
if (isset($data['id'])) {
$this->where('id="'.$data['id'].'"');
unset($data['id']);
}
foreach ($data as $key => $value) {
$sql .= "`$key`='$value', ";
}
$sql = rtrim($sql, ', ') . ' ';
$sql .= $this->options['where'];
$this->sql($sql);
$result = $this->connect->exec($sql);
}
public function sql($sql='')
{
if (!empty($sql)) {
$this->sql[] = $sql;
} else {
return $this->sql;
}
}
}
大概就是这样，只是我的提交页面，套用的我的工具箱，大概是这样的。
最后是查询