自建库短链接生成

短链接 - 自建Mysql数据库版（非第三方）

本篇文章适合自己有想法建库和自己有短域名的小伙伴

可以学到的技术：

实现短链接生成（62进制）
mysqli预处理操作数据库（查、增）
使用Apache伪静态.htaccess配置重定向规则

开发环境：

PHPStorm2019.2

Apache2.4
MySQL5.6
PHP 7.x

项目文件目录结构如下：

文件名	用途
index.php	入口文件
link.php	生成短链接
other.php	定义各个类
.htaccess	Apache伪静态

创建用于储存短链接及长链接数据的数据表：

CREATE TABLE `短链接` (
  `序号` int(11) NOT NULL AUTO_INCREMENT,
  `长链接` text,
  `短链接` text,
  `主动` text,
  `增加时间` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`序号`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4

index.php

<?php
define("domain", "sls.fun");
use other\mysql\mysql;
$page = $_GET['page'];
if (strlen($page) != 6) {
    die("<h1>" . domain . "</h1>");
}
require_once "other.php";
$mysql = new mysql();
$longLink = $mysql->get_longLink($page);
if ($longLink == false || $longLink == "") {
    die("<h1>" . domain . "</h1>");
}
header("location:" . $longLink);
return;

link.php

<?php
/**
 * 生成短链接
 *
 * @author Shawna<1070175533@qq.com>
 * @since 1.0
 */
define("domain", "sls.fun");
$request_json = $_POST['request'];
if ($request_json == "") {
    die(json_encode(array(
        "code" => "-1",
        "msg" => "缺少参数request",
    )));
}
$request = json_decode($request_json, true);
if ($request['link'] == "") {
    die(json_encode(array(
        "code" => "-1",
        "msg" => "json缺少参数link",
    )));
}
require_once "other.php";
use other\mysql\mysql;
use other\tool\tool;
$mysql = new mysql();
$tool = new tool();
if ($tool->verifyUrl($request['link']) != 200) {
    die(json_encode(array(
        "code" => -1,
        "msg" => "URL访问超时或无法访问",
    )));
}
$Hex62 = $tool->Hex62(crc32($request['link']));
$longLink = $mysql->get_longLink($Hex62);
if ($longLink == false || $longLink == "") {
    if ($mysql->set_link($request['link'], $Hex62, $_SERVER['REMOTE_ADDR'])) {
        die(json_encode(array(
            "code" => 1,
            "msg" => domain . "/" . $Hex62,
        )));
    } else {
        die(json_encode(array(
            "code" => -1,
            "msg" => "服务器繁忙",
        )));
    }
}
die(json_encode(array(
    "code" => 1,
    "msg" => domain . "/" . $Hex62,
)));

other.php

<?php
namespace other\mysql;
use mysqli;
class mysql
{
    private $mysql_ip = "";//数据库IP
    private $mysql_user = "";//用户名
    private $mysql_password = "";//密码
    private $mysql_dbname = "";//数据库名
    private $mysqli;//储存数据库连接句柄
    private $sql;//储存sql语句
    //构造函数
    public function __construct()
    {
        $this->mysqli = @new mysqli($this->mysql_ip, $this->mysql_user, $this->mysql_password, $this->mysql_dbname);//mysqli连接数据库
        if (mysqli_connect_errno()) {
            die(json_encode(array(
                "code" => -1,
                "msg" => "Mysqli连接失败: " . mysqli_connect_error(),
            )));//连接失败结束
        }
        $this->sql = array(
            "SetLink" => "insert into `短链接`(`长链接`,`短链接`,`主动`) values(?, ?, ?);",//写短链接长链接到数据库
            "GetLongLink" => "SELECT `长链接` FROM `短链接` WHERE `短链接` = ?;",//短链接查长链接
        );
    }
    //析构函数
    public function __destruct()
    {
        // TODO: Implement __destruct() method.
        mysqli_close($this->mysqli);//断开mysql连接
    }
    /**
     * 写短链接到数据表
     *
     * @access public
     * @param string $l_link 长链接
     * @param string $s_link 短链接
     * @param string $host 主动
     * @return boolean
     * @since 1.0
     */
    function set_link($l_link, $s_link, $host)
    {
        $stmt = $this->mysqli->prepare($this->sql['SetLink']);
        $stmt->bind_param("sss", $l_link, $s_link, $host);
        if (!$stmt->execute()) {
            $stmt->close();
            return false;
        }
        $stmt->close();
        return true;
    }
    /**
     * 短链接查长链接
     *
     * @access public
     * @param string $s_link 短链接
     * @return string|boolean
     * @since 1.0
     */
    function get_longLink($s_link)
    {
        $stmt = $this->mysqli->prepare($this->sql['GetLongLink']);
        $stmt->bind_param("s", $s_link);
        $stmt->bind_result($result);
        if (!$stmt->execute()) {
            $stmt->free_result();
            $stmt->close();
            return false;
        }
        $tmp = "";
        while ($stmt->fetch()) {
            $tmp = $result;
        }
        $stmt->free_result();
        $stmt->close();
        return $tmp;
    }
}
namespace other\tool;
class tool
{
    /**
     * 转62进制
     *
     * @access public
     * @param string $data 10进制数据
     * @return string
     * @since 1.0
     */
    function Hex62($data)
    {
        $base = array(
            '0', '1', '2', '3', '4', '5', '6', '7', '8', '9',
            'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j',
            'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't',
            'u', 'v', 'w', 'x', 'y', 'z', 'A', 'B', 'C', 'D',
            'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N',
            'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X',
            'Y', 'Z',
        );
        $result = "";
        do {
            $result = $base[$data % 62] . $result;
            $data = intval($data / 62);
        } while ($data != 0);
        return $result;
    }
    /**
     * 验证URL是否有效
     *
     * @access public
     * @param string $url url地址
     * @return string
     * @since 1.0
     */
    function verifyUrl($url)
    {
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
        curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
        $response = curl_exec($ch);
        return curl_getinfo($ch, CURLINFO_HTTP_CODE);
    }
}

.htaccess

RewriteEngine On
RewriteCond %{REQUEST_URI} !\.(css|js|png|jpe?g)$
RewriteCond $1 !^(index.php|favicon.ico|404.html|link.php)
RewriteRule ^(.*)$ http://sls.fun/?page=$1 [R=302,L]

Shawna
2020年4月14日