SQL 兩點經緯度求距離

參考資料 Fastest Way to Find Distance Between Two Lat/Long Points裡的 Binary Worrier 解法。

使用 MariaDB 10，測試的 DB table 為 locations

CREATE TABLE `locations` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `lat` double NOT NULL COMMENT '緯度',
  `lng` double NOT NULL COMMENT '經度'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `locations` (`id`, `name`, `lat`, `lng`) VALUES
(1, '南勢角捷運站', 24.990508728072932, 121.509155455015),
(2, '景安站', 24.993901994056493, 121.50479966768725),
(3, '台大門口', 25.016772139171792, 121.53351504607843);

ALTER TABLE `locations`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `locations`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

-- 測試用的 SQL
-- 以南勢角捷運站為參考位置，求 3 公里以內的景點
SELECT *, 
       ( 6371 * ACOS( COS( RADIANS(24.990508728072932) ) 
              * COS( RADIANS( `lat` ) ) 
              * COS( RADIANS( `lng` ) - RADIANS(121.509155455015) ) 
              + SIN( RADIANS(24.990508728072932) ) 
              * SIN( RADIANS( `lat` ) ) ) ) AS `distance` 
from `locations` 
HAVING `distance` < 3 OR `distance` IS NULL
ORDER BY `distance`;

Debian 10 安裝 MySQL 8

參考資料： https://computingforgeeks.com/how-to-install-mysql-8-0-on-debian

先更新 repo 資料，並安裝 wget

sudo apt update
sudo apt -y  install wget

到 https://repo.mysql.com 查看官方 apt repo 資料，例如 mysql-apt-config_0.8.19-1_all.deb，下載並安裝。

wget https://repo.mysql.com/mysql-apt-config_0.8.19-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.19-1_all.deb

選擇預設設定即可，選「OK」並按 Enter。接著直接安裝，安裝過程要設定 root 密碼，還有選擇密碼加密的方式，建議使用舊的方式，和 5.X 相容的模式會比較方便：

sudo apt update
sudo apt -y install mysql-server

確認安裝的版本，即可登入：

apt policy mysql-server
mysql -uroot -p

查看伺服器狀態、關閉、啟動：

sudo systemctl status mysql
sudo systemctl stop   mysql
sudo systemctl start  mysql

Raspbian 安裝 MariaDB

查看 Raspbian 提供支援 MariaDB 的版本：

pi@raspberrypi:~ $ apt-cache policy mariadb-server
mariadb-server:
  已安裝：(無)
  候選： 1:10.3.22-0+deb10u1
  版本列表：
     1:10.3.22-0+deb10u1 500
        500 http://raspbian.raspberrypi.org/raspbian buster/main armhf Packages

安裝：

sudo apt install mariadb-server

安裝完成後，使用下式連線：

sudo mysql -u root

如果有需要，建立外連的用戶：

-- 建立本地端可以連線的用戶
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'newuser_pw';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

-- 建立其他主機可以連線的用戶
CREATE USER 'newuser2'@'%' IDENTIFIED BY 'newuser_pw2';
GRANT ALL PRIVILEGES ON * . * TO 'newuser2'@'%';
FLUSH PRIVILEGES;

從 mac 用 adminer.php（Apache/PHP 環境）連到 R-Pi，結果得到 connection refused，查看網路狀況：

sudo netstat -ln

得到：

# sudo netstat -ln

Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN

爬文得知要設定 bind-address=0.0.0.0，到 /etc/mysql/my.cnf 設定結果無效。 後來才找到在 /etc/mysql/mariadb.conf.d/50-server.cnf 有設定 bind-address=127.0.0.1 ，變更之後，重新啟動：

sudo service mysqld restart

就可以由外部連入：

# sudo netstat -ln
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN

Flask 新增資料到 MySQL

本文的參考專案 https://github.com/shinder/flask-practice

承上篇，這裡要使用 Postman POST 傳送 JSON 文件，然後 Flask 接收後寫入資料庫。

MySQL 官網新增資料的範例

Flask route 寫法：

@app.route('/receive-json', methods=['POST'])
def receive_json():
    (cursor, cnx) = modules.mysql_connection.get_cursor()
    data = json.loads(request.get_data())  # JSON 字串轉換為 dict
    p = {}
    sids = []  # 用來記錄新增的 primary key
    p['name'] = data['name'] if 'name' in data else ''
    p['email'] = data['email'] if 'email' in data else ''
    p['mobile'] = data['mobile'] if 'mobile' in data else ''
    p['birthday'] = data['birthday'] if 'birthday' in data else '1900-01-01'
    p['address'] = data['address'] if 'address' in data else ''

    # 兩種作法
    sql1 = ("INSERT INTO `address_book`"
        "(`name`, `email`, `mobile`, `birthday`, `address`, `created_at`"
        ") VALUES (%s, %s, %s, %s, %s, NOW())")
    sql2 = ("INSERT INTO `address_book`"
        "(`name`, `email`, `mobile`, `birthday`, `address`, `created_at`"
        ") VALUES (%(name)s, %(email)s, %(mobile)s, %(birthday)s, %(address)s, NOW())")

    cursor.execute(sql1, (p['name'], p['email'], p['mobile'], p['birthday'], p['address']))
    sids.append(cursor.lastrowid)  # 取得新增項目的 primary key
    cursor.execute(sql2, p)  # 使用 dict
    sids.append(cursor.lastrowid)
    cnx.commit()  # 提交新增的資料才會生效

    return jsonify(sids)  # 輸出 JSON 格式

Postman 發需求的網址 http://localhost:5000/receive-json，JSON 文件如下：

{
    "address": "台南市",
    "birthday": "2000-11-22",
    "email": "wwww@test.com",
    "mobile": "0918777-777",
    "name": "陳小華"
}

Flask 使用 MySQL Connector

本文的參考專案 https://github.com/shinder/flask-practice

專案資料表 address_book 參考

連線 MySQL DB 的套件，這邊介紹最陽春的，就是 MySQL 官方出的 mysql-connector。可以依照 mysql-connector 開發人員指引 介紹的方式安裝。用 pip 安裝應該是最簡單的：

pip install mysql-connector

連線的功能我們把它獨立出來成為一個模組 app/modules/mysql_connection.py，其中 get_cursor() 可以同時回傳游標物件和連線物件：

import mysql.connector

connect_data = {
    'host': 'localhost',
    'user': 'root',
    'passwd': 'root',
    'database': 'test'
}
cnx = None

def get_connection():
    global cnx  # 將連線物件存放在全域變數
    if not cnx:
        cnx = mysql.connector.connect(**connect_data)
        return cnx
    else:
        return cnx

def get_cursor():
    cursor = get_connection().cursor(dictionary=True)  # 讀出資料使用 dict，預設為 tuple
    return (cursor, get_connection())  # 同時回傳 cursor 和 connection

在主檔案定義 route：

import modules.mysql_connection

@app.route('/try-mysql')
def try_mysql():
    (cursor, cnx) = modules.mysql_connection.get_cursor()
    sql = ("SELECT * FROM address_book")
    cursor.execute(sql)
    return render_template('data_table.html', t_data=cursor.fetchall())

樣版檔 app/templates/data_table.html：

    <tbody>
        {% for i in t_data %}
        <tr>
            <td>{{ i.name }}</td>
            <td>{{ i.email }}</td>
        </tr>
        {% endfor %}
    </tbody>

NodeJS 將 session 資料存入 MySQL

一般使用 express.js 時，使用的 session 套件為 express-session。使用記憶體存放 session 資料的做法：

const session = require('express-session');

app.use(session({
    saveUninitialized: false,
    resave: false,
    secret: '你的 cookie 加密字串',
    cookie: {
        maxAge: 1200000  // 單位為毫秒
    }
}));

若要將 session 存入資料庫，需要先安裝 express-mysql-session 套件。設定方式如下，其中的 db_connect2.js 請看 上篇：

const session = require('express-session');
const MysqlStore = require('express-mysql-session')(session);
const db = require(__dirname + '/db_connect2');

const sessionStore = new MysqlStore({}, db);
app.use(session({
    saveUninitialized: false,
    resave: false,
    secret: '你的 cookie 加密字串',
    store: sessionStore,
    cookie: {
        maxAge: 1200000
    }
}));

若使用 session 可以在資料庫看到這樣的資料：

CREATE TABLE IF NOT EXISTS `sessions` (
  `session_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `expires` int(11) unsigned NOT NULL,
  `data` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `sessions` (`session_id`, `expires`, `data`) VALUES
('8CDH6O91CkkY_1DpJs7h3YmzbqgQeqrF', 1588332706, '{"cookie":{"originalMaxAge":1200000,"expires":"2020-05-01T11:31:42.263Z","httpOnly":true,"path":"/"},"hello":"shinder"}');

ALTER TABLE `sessions`
  ADD PRIMARY KEY (`session_id`);

NodeJS 使用 mysql2 連線 MySQL

Node 連線 MySQL 資料庫，常用的套件為 mysql 和 mysql2。mysql 是比較資深的套件，但缺點是沒有直接支援 Promise，所以在使用上若要使用 Promise 需要使用 bluebird 之類的套件。

mysql2 標榜更快，支援 Promise。以下為連線的 module ( db_connect2.js )：

const mysql = require('mysql2');
const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'test',
    waitForConnections: true,
    connectionLimit: 10,  // 最大連線數
    queueLimit: 0
});
module.exports =  pool.promise();  // 滙出 promise pool

在 express.js 使用上的例子：

const db = require(__dirname + '/db_connect2');

app.get('/try-db', (req, res)=>{
    const sql = "SELECT * FROM address_book LIMIT 3";
    db.query(sql).then(([results, fields])=>{
        res.json(results);
    });
});

VSCode 簡便的 MySQL 管理外掛

VSCode 外掛 MySQL (MySQL management tool)
作者: Jun Han
外掛 ID: formulahendry.vscode-mysql

算是簡單易用的 MySQL 管理工具

MySQL 剔除某欄重複的資料

使用 GROUP BY：
SELECT * FROM `some_table` GROUP BY `some_field`

將結果寫入另一張表：

CREATE TABLE `tmp_table` AS (
SELECT * FROM `some_table` GROUP BY `some_field`
)


使用 DISTINCT 只能用於某一欄或著某些欄位：
SELECT DISTINCT `some_field` FROM `some_table`
SELECT DISTINCT `some_field`, `another_field` FROM `some_table`
SELECT DISTINCT * FROM `some_table` （含主鍵的話，此行無用）

另外，計算重複的個數：
SELECT *, count(*) FROM `some_table` GROUP BY `some_field`

承上篇「無法使用phpMyAdmin時的DB資料備份及回復」

sqlbuddy 試用了一下, 功能雖然較 phpMyAdmin 簡單許多, 但其實已經很夠用了 :)

無法使用phpMyAdmin時的DB資料備份及回復

在某些情況，無法使用 phpMyAdmin，資料庫操作時都會挷手挷腳。
資料備份可以使用 Old Guy Mybackup，只有一個檔案，加密碼設定一下連線帳號資料，再用瀏覽器連一下就 OK 了，很方便。
回復資料的話，用 BigDump。briian 的介紹、阿修的介紹。

如何選擇 MySQL 的儲存引擎

MySQL Storage Engines 值得細細品味!
簡單的講, 要快要方便用 MyISAM, 要 Transaction-safe 用 innoDB。

INNER JOIN 的效能 (SQL)

個人經驗是 JOIN 三張表以上, 效能明顯變慢。可以使用 sub select 來達到相同的目的。
用一個資料表開得不好的例子來說明。
第一種:

SELECT a. * , ch.listname AS chl, cn.listname AS cnl, en.listname AS enl, jp.listname AS jpl, kr.listname AS krl
FROM aa_mainmenu a
INNER JOIN ch_mainmenu ch ON a.sno = ch.a_id
INNER JOIN cn_mainmenu cn ON a.sno = cn.a_id
INNER JOIN en_mainmenu en ON a.sno = en.a_id
INNER JOIN jp_mainmenu jp ON a.sno = jp.a_id
INNER JOIN kr_mainmenu kr ON a.sno = kr.a_id
ORDER BY a.priority DESC

第二種:

SELECT *
FROM (

 SELECT a. * , b.listname AS kr
 FROM aa_mainmenu a
 INNER JOIN kr_mainmenu b ON a.sno = b.a_id
)a
INNER JOIN (

 SELECT a. * , b.en, b.jp
 FROM (

  SELECT a.a_id, a.listname AS ch, b.listname AS cn
  FROM ch_mainmenu a
  INNER JOIN cn_mainmenu b ON a.a_id = b.a_id
 )a
 INNER JOIN (

  SELECT a.a_id, a.listname AS en, b.listname AS jp
  FROM en_mainmenu a
  INNER JOIN jp_mainmenu b ON a.a_id = b.a_id
 )b ON a.a_id = b.a_id
)b ON a.sno = b.a_id
ORDER BY a.priority DESC

第二種看起來較複雜, 但執行效能通常比第一種好。