20200908のMySQLに関する記事は5件です。

【Laravel】ラズパイのDBと連携させる

ラズパイ側の操作とLravel側の操作に分けて解説していく。

DBでのユーザの作成と権限付与は別々でやる

ラズパイ側の操作

DB操作用ユーザ作成

 CREATE USER 'user'@'%' IDENTIFIED BY 'pass';

%を使用することでどんなホストでも接続ができる

権限確認

登録されているユーザ名を確認

select user,host from mysql.user;

特定ユーザの権限確認

show grants for 'ユーザ名'@'ホスト名';

ユーザ名とホスト名は'"で囲むこと

権限付与

形式
GRANT [権限] ON [レベル] TO [ユーザ] IDENTIFIED BY PASSWORD '[パスワード]'
権限は
  • select
  • update
  • insert
  • delete
  • all
レベル 説明
GRANT [権限] ON *.* TO [ユーザ] グローバルレベル
GRANT [権限] ON [DB名].* TO [ユーザ] データベースレベル
GRANT [権限] ON [DB名].[テーブル名] TO [ユーザ] テーブルレベル
GRANT [権限](カラム1,カラム2,...) ON [DB名].[テーブル名] TO [ユーザ] カラムレベル

ポートの確認

show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+

Laravel側の操作

後は今まで得た情報を.envdatabase.phpに反映するだけ
ここでは.envだけ書いておく

.env
DB_CONNECTION=mysql
DB_HOST=IPアドレス
DB_PORT=3306
DB_DATABASE=DB名
DB_USERNAME=作成したユーザ名
DB_PASSWORD=パスワード

参考

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

CakePHP3のfindでcontain要素に検索条件を追加する

例えばUsersテーブルにUserInfoテーブルが紐付いていて、prefecture_id=2で名字が佐藤か工藤の1名をFindしたいときは下記のようになります。

useで$prefecture_idをわたしてあげるのがミソです。

UsersController.php
$prefecture_id = 2;
$user = $this->Users->find()->contain(
    ['UserInfo' => function($q) use ($prefecture_id) {
        return $q->where(['UserInfo.prefecture_id' => $prefecture_id]);
    }])
    ->where([
        'Users.last_name IN' => ['佐藤', '工藤']
    ])
    ->first();

かんたんですね。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

Gormを使ってみた(ORMライブラリでMySQL接続)

概要

  • GoでMySQL接続をしようと思っていた時に、「gorm」というORMライブラリを見つけたので使ってみた
  • 公式のドキュメントを参考に実装時に必要になった知識をまとめていきます

準備

gormをインストールします
またMySQLと接続するためのDriverもインストールします

go get -u gorm.io/gorm
go get -u gorm.io/driver/mysql

DB接続

  • gorm.Openを使用します。
  • パスワードのみ環境変数から取得するようにしてみました。
import (
    "os"

    "gorm.io/driver/mysql"
    "gorm.io/gorm"
)

USER := "gorm"
// PASS := os.Getenv("MYSQL_PASSWORD")
PASS := "gorm"
HOST := "localhost"
PORT := "3306"
DBNAME := "gorm"

DSN := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=utf8mb4&parseTime=True&loc=Local", USER, PASS, HOST, PORT, DBNAME)
connection, err := gorm.Open("mysql", DSN)

if err != nil {
    panic(err.Error())
}

以降、DB操作時にここで作成したconnectionを使用します

CRUD

準備

まずModelとなる構造体から作成します。

  • 主キーとなるフィールドが必須です。
  • 主キーとなるフィールドにはgorm:"primaryKey"というタグを付与します
    • デフォルトではIDが主キーとして認識されています
    • なので以下の構造体だとタグはなくても同じです?
  • フィールドはキャメルケース(CamelCase)で定義します
type User struct {
    Id   int `gorm:"primaryKey"`
    Name string
}

次にテーブルを作成します。

  • テーブル名はモデルの複数形にします
  • 主キーの設定が必須です
    • 主キーをベースにUpdate,Deleteが実行されるため、主キーがないと全件操作されてしまいます
  • 構造体のフィールドをスネークケース(snake_case)に変換したもので定義します
create table users (
  id   int primary key,
  name varchar(10)
);

Create (insert)

  • Createメソッドを使用します
  • 引数にはModelのポインタを渡します
user := User{Id: 1, Name: "User-1"}
connection.Create(&user)
mysql> select * from users;
+----+--------+
| id | name   |
+----+--------+
|  1 | User-1 |
+----+--------+

一意制約エラーの場合

Insert時に気にすることの代表格として一意制約エラーがありますが、
SQLでエラーとなった場合には以下のようにErrorを確認できます
※一意制約エラー以外でも同様です。

user := User{Id: 1, Name: "User-1"}
result := connection.Create(&user1)
fmt.Println(result.Error)
// Error 1062: Duplicate entry '1' for key 'PRIMARY'

auto_incrementを利用している場合

Idにauto_incrementを使用している場合、Idを空にしておくことで機能を使用することができます。
またgormの機能により、採番された番号がオートフィルされます!

user := User{Name: "User-1"}
fmt.Println(user)  // {0 User-1}
connection.Create(&user)
fmt.Println(user)  // {1 User-1}

Read (select)

USERSテーブルに以下の4件のレコードがある状態で実行していきます。

mysql> select * from users;
+----+--------+
| id | name   |
+----+--------+
|  1 | User-4 |
|  2 | User-3 |
|  3 | User-2 |
|  4 | User-1 |
+----+--------+

用意されているメソッド

基本的にメソッドチェーンで記述していきます。
以下のようなメソッドが用意されています。

メソッド サンプル SQL展開イメージ
Find Find(&users) ※リストを取得するための終端操作
First First(&user) order by id limit 1
※単一行を取得するための終端操作
last Last(&user) order by id desc limit 1
※単一行を取得するための終端操作
Take Take(&user) limit 1
※単一行を取得するための終端操作
Select Select("id", "name") select id, name
Distinct Distinct("name") select distinct name
Joins Joins("left join users on hoge.id = users.id") left join users on hoge.id = users.id
Table Table("users") from users
Where Where("id = ?", 1) where id = 1
Or Or("name = ?", "User-1") or name = 'User-1'
Not Not("id = ?", 1) where not id = 1
Order Order(id desc) order by id desc
Limit Limit(5) limit 5
Offset Offset(2) offset 2
Group Group("name") group by name
Having Having("count >= ?", 5) having count >= 5
Raw Raw("select * from users") ※SQLを直接指定する場合に使用
Scan Scan(&users) ※Raw使用時等にレコードを格納する構造体を指定するために使用

以下これらのメソッドを使用して実際にレコードを取得するサンプルです。

全件取得

users := []User{}
connection.Find(&users)
// select * from users
fmt.Println(users)
// [{1 User-4} {2 User-3} {3 User-2} {4 User-1}]

主キー検索

FindFirstの第2引数に検索条件を渡すせばOK!

一致
user := User{}
connection.First(&user, 1)
// select * from users where id = 1 limit 1
fmt.Println(user)
// {1 User-4}
in句
users := []User{}
connection.Find(&users, []int{1, 2})
// select * from users where id in (1, 2)
fmt.Println(users)
// [{1 User-4} {2 User-3}]

補足:Firstはlimit 1が付与されます。

条件指定

WhereOrメソッドを使うことでクエリを表現できます。
それぞれ

  • 第一引数:検索条件
  • 第二引数以降:バインドする値

というように指定します。

user := User{}
users := []User{}

connection.Where("id = ?", 1).First(&user)
// select * from users where id = 1
fmt.Println(user)
// {1 User-4}

connection.Where("id IN ?", []int{1, 2}).Find(&users)
// select * from users where id in (1, 2)
fmt.Println(users)
// [{1 User-4} {2 User-3}]

connection.Where("id = ?", 1).Or("name = ?", "User-1").Find(&users)
// 同じ:connection.Where("id = ? or name = ?", 1, "User-1").Find(&users)
// select * from users where id = 1 or name = 'User-1'
fmt.Println(users)
// [{1 User-4} {4 User-1}]

構造体を用いて検索

構造体に検索項目を埋めてWhereに指定することでも条件指定の検索ができます!
ただし、0, '', falseは値が設定されていないという判定がされるため、使用できないようです

users := []User{}
user := User{Name: "User-1"}
connection.Where(&user).Find(&users)
// select id, name from users where name = 'User-1'
fmt.Println(users)
// [{4 User-1}]

SQLを直接記述

SQLをRawで、結果を格納する構造体をScanで指定します

users := []User{}
connection.Raw("select id, name from users where id = ?", 1).Scan(&users)
// select id, name from users where id = 1
fmt.Println(users)
// [{1 User-4}]

Update

Updateには以下の2つのメソッドを使用します
※公式の説明ではなく個人の見解です?

メソッド サンプル 使い分け
Save .Save(&user) selectで取得した構造体の一部を更新したいとき
Update .Model(&user).Update("name", "updated name") 条件やカラムを指定して更新したいとき

以下のようなテーブルの状態でサンプルを実装・実行していきます

mysql> select * from users;
+----+--------+
| id | name   |
+----+--------+
|  1 | User-4 |
|  2 | User-3 |
|  3 | User-2 |
|  4 | User-1 |
+----+--------+

Save

user := User{}
// 「ID = 1」で検索
connection.Where("id = ?", 1).First(&user)
fmt.Println(user)
// {1 User-4}

// Nameを「User-X」に更新
user.Name = "User-X"
connection.Save(&user)

// 「ID = 1」で検索
connection.Where("id = ?", 1).First(&user)
fmt.Println(user)
// {1 User-X}

Update(単一カラム)

単一カラムを更新したい場合は引数に「更新カラム」と「更新後の値」を設定してUpdateメソッドを呼び出します
またModelで指定した構造体に更新後の値が自動で設定されます

user := User{Id: 1}
fmt.Println(user)
// {1 }

// nameを「User-X」に更新
connection.Model(&user).Update("name", "User-X")
fmt.Println(user)
// {1 User-X}

Updates(複数カラム)

ここではサンプルのテーブルの構成上、単一カラムの更新となっていしまいますが、
構造体の更新したいフィールドに更新後の値を設定してUpdatesメソッドに渡すことで複数カラムの更新が可能です。

// 検索用にIDを指定
userForSearch := User{Id: 1}
fmt.Println(userForSearch)

// 更新したい内容を別の構造体に定義
userForUpdate := User{Name: "User-X"}
// 同じ: map[string]interface{}{"name": "User-X"}

// 更新
connection.Model(&userForSearch).Updates(&userForUpdate)
// update users set id = 1 where name = 'User-X'
fmt.Println(userForSearch)
// {1 User-X}

複数行の更新

  • Whereで更新条件を指定してUpdatesを実行します
  • 全件更新の場合にも1 = 1等で条件指定が必要です
connection.Table("users").Where("name like ?", "User-%").Updates(User{Name: "User-X"})
// 「.Table("users")」は「.Model(User{})」でも可

users := []User{}
connection.Find(&users)
fmt.Println(users)

計算式を用いて更新後の値を表現する

gorm.Exprを使用すると固定値以外を更新後の値に設定することができます

user := User{}
// gorm.Exprで計算式を記述
connection.Model(&user).Update("name", gorm.Expr("concat('User-', id)"))
// 全件取得
users := []User{}
connection.Find(&users)
fmt.Println(users)
// [{1 User-1} {2 User-2} {3 User-3} {4 User-4}]

SQLを指定して更新

// SQLを指定して更新
connection.Exec("update users set name = 'User-X'")
// 全件取得
users := []User{}
connection.Find(&users)
fmt.Println(users)
// [{1 User-X} {2 User-X} {3 User-X} {4 User-X}]

更新件数・結果

Updateの戻り値を取得し、RowsAffectedErrorで確認できます

result := connection.Model(&user).Update("name", "User-X")
fmt.Println(result.RowsAffected)
// 1
fmt.Println(result.Error)
// <nil> ※エラーなしのためNil

Delete

以下のようなテーブルの状態でサンプルを実装・実行していきます

mysql> select * from users;
+----+--------+
| id | name   |
+----+--------+
|  1 | User-4 |
|  2 | User-3 |
|  3 | User-2 |
|  4 | User-1 |
+----+--------+

1件削除

Deleteメソッドに主キー項目が設定された構造体を渡して実行します

// 削除
user := User{Id: 1}
connection.Delete(&user)
// delete from users where id = 1

users := []User{}
connection.Find(&users)
fmt.Println(users)
// [{2 User-3} {3 User-2} {4 User-1}]

主キー以外の条件指定

Whereメソッドを使用して条件を指定します

// 削除
connection.Where("id >= ?", 3).Delete(&User{})
// delete from users where id >= 3

users := []User{}
connection.Find(&users)
fmt.Println(users)
// [{1 User-4} {2 User-3}]

論理削除/物理削除

以下の条件がそろっているときにDeleteはデフォルトで論理削除を実行します

  • テーブルにdeleted_at datetimeカラムがある
  • 構造体にDeletedAt gorm.DeletedAtフィールドがある

論理削除されたレコードはUnscopedメソッドを実行してDeleteを実行することで物理削除されます

user := User{}
connection.Where("id = ?", 1).First(&user)

// 論理削除
connection.Delete(&user)
// update users set deleted_at="2020-09-08 22:21:43" where id = 1

connection.Unscoped().Where("id = ?", 1).First(&user)
fmt.Println(user)
// {1 User-4 {2020-09-08 22:21:43 +0900 JST true}}

// 物理削除
connection.Unscoped().Delete(&user)
// delete from users where id = 1

result := connection.Unscoped().Where("id = ?", 1).First(&user)
fmt.Println(result.Error)
// record not found

トランザクション管理

例えば①②③の順で実行されることで一貫性が保てる以下のような処理について

connection.Create(&user1)  // ①
connection.Save(&user2)    // ②
connection.Delete(&user3)  // ③

1行ずつ実行ごとにcommitがされるため、仮に③で失敗すると①②がロールバックされず一貫性が保てなくなります。

このような場合はTransactionメソッドを使用すると制御が可能になります

  • *gorm.DBを引数にとり、errorを返す関数を渡します
  • errorを返すとブロック内の処理がrollbackされます
  • nilを返すとcommitされます
connection.Transaction(func(tx *gorm.DB) error {
    if err := tx.Create(&user1).Error; err != nil {
        return err  // rollback
    }
    if err := tx.Save(&user2).Error; err != nil {
        return err  // rollback
    }
    if err := tx.Delete(&user3).Error; err != nil {
        return err  // rollback
    }

    return nil  // commit
})
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

小ネタ/MySQL 8.0 のダブルライトバッファがどのバージョンで暗号化されるようになったのか調べた

以前、MySQL 8.0 ではテーブルスペースや REDO ログ/UNDO ログ・システムテーブルを暗号化しても、ダブルライトバッファには対象データが平文で書き出されていました。

先日、MySQL 関連のオンラインイベントの Q&A で、

という話を聞いたので、8.0.17 から順番に確認していきました。

確認内容

  • CentOS 8.2 64 ビット環境で実行
  • MySQL 8.0 Community Server の対象バージョンを dnf インストール
  • /etc/my.cnf に以下の内容を追記して起動
my.cnf追記部分
early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql-keyring/keyring
innodb_redo_log_encrypt=1
innodb_undo_log_encrypt=1

server-id=1
binlog_format=MIXED
binlog_encryption=ON
binlog_rotate_encryption_master_key_at_startup=ON
  • mysql_secure_installationを実行
  • MySQL に暗号化有効でデータを入れる
MySQLで操作
mysql> ALTER TABLESPACE mysql ENCRYPTION = 'Y';
Query OK, 0 rows affected (2.12 sec)

mysql> CREATE DATABASE encryption_test;
Query OK, 1 row affected (0.01 sec)

mysql> USE encryption_test;
Database changed
mysql> CREATE TABLE enc_test (id INT PRIMARY KEY AUTO_INCREMENT, value VARCHAR(100)) ENGINE=innodb ENCRYPTION='Y';
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO enc_test SET value='234567890ABCDEFGHIJKLMNOPQRSTabcdefghijklmnopqrst1234567890ABCDEFGHIJKLMNOPQRSTabcdefghijklmnopqrst';
Query OK, 1 row affected (0.00 sec)

(計 5 行挿入)

mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE enc_test SET value='ENCRYPTED' WHERE id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
  • ファイル検索で確認
ファイル検索
# fgrep -r 'ENCRYPTED' /var/lib/mysql

結果

MySQL 8.0.17

確認(8.0.17)
# fgrep -r 'ENCRYPTED' /var/lib/mysql
Binary file /var/lib/mysql/ibdata1 matches

8.0.16 の時と同様に見つかりました。

※バイナリエディタで開いてみたところ、対象のデータが(更新前の行に上書きされる形で)見つかりました(ここでは省略)。

MySQL 8.0.18

確認(8.0.18)
# fgrep -r 'ENCRYPTED' /var/lib/mysql

今度は見つかりませんでした。

MySQL 8.0.19 以降も同じ結果でした。

MySQL 8.0.18 から暗号化されるようになったことがわかりました。

余談

テストに使ったテーブル名を、

テーブル名でファイル検索
# fgrep -r 'enc_test' /var/lib/mysql

で検索してみたところ、どのバージョンでもテーブル名が見つかりました。

テーブル名確認(8.0.17~19)
# fgrep -r 'enc_test' /var/lib/mysql
Binary file /var/lib/mysql/ibdata1 matches
テーブル名確認(8.0.20~21)
# fgrep -r 'enc_test' /var/lib/mysql
Binary file /var/lib/mysql/#ib_16384_0.dblwr matches

バイナリエディタで開いてみたところ、いずれも SQL 文やテーブル定義ではありませんでしたが、いくつかデータベース名やテーブル名が平文で保管されている様子でした。


  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

bindValue()の仮引数は何を示しているのか?

この記事でわかること

  • PHPのPDOクラスの関数bindValue()の仮引数の中身がわかる

そもそもbindValue()とは

public PDOStatement::bindValue ( mixed $parameter , mixed $value [, int $data_type = PDO::PARAM_STR ] ) : bool

プリペアドステートメントで使用する SQL 文の中で、 対応する名前あるいは疑問符のプレースホルダに値をバインドします。
 PHP公式リファレンス

ものすごく雑に要約すると、bindValue()とは、
下記SQLクエリ文の"?"部分を別の文字列に置換することができる関数です。
SQLインジェクション対策ですね。

$sql = "insert into phptodo (name, done, priority) values (?, 0, ?)";

特に、プリペアドステートメント?PDO?な方は、こちらの記事にまとまっているので参照すると幸せになれます。

SQLインジェクション?という方は、安全なWebサイトの作り方を参照すると幸せになれます。

なお、bindValue()は第三引数まで取ります。

bindValue()の各引数について考える

サンプルコード

     $name = "hoge"
     $priority = "high"

     $sql = "insert into phptodo (name, done, priority) values (?, 0, ?)";  // SQLインジェクション対策のプレースホルダ(=?)
     $stmt = $dbh->prepare($sql);

     $stmt->bindValue(1, $name, PDO::PARAM_STR);

     $stmt->bindValue(2, $priority, PDO::PARAM_STR);
     $stmt->execute();

// $sql = "insert into phptodo (name, done, priority) values ("hoge", 0, "high")"
// となる


bindValue()の第一引数

どのプレースホルダ( $sql 内の"?"のこと ) に関数を適用するかを数字で指定します。

例えばサンプルコードでは values(?, 0, ?)となっているので、

values内の第一引数の "?" に変更を加えたい場合 => 1
values内の第三引数の "?" に変更を加えたい場合 => 2

と記述します。
数字は0ではなく1から始まるので注意!

bindValue()の第二引数

第一引数で指定したプレースホルダに何を代入するかを指定します。
ここでは変数 $name$priority を代入しています。

bindValue()の第三引数

第二引数で指定した内容をどのようなデータ型にするかを指定します。

PDO::PARAM_INT PDO::PARAM_STRなど

これらはInteger型の定数で、例えばPDO::PARAM_STRはInteger型の2を表しています。
(このため、PDO::PARAM_STRではなく2と記述しても同等の内容が実行されます)

print(PDO:PARAM_STR)

// 2

他の定数はここ

プレースホルダに"?"を使わない方法

蛇足ですが、プレースホルダを任意の文字列にして可読性を高める方式もあります。

SQL文の"?"を:hoge:fugaとして、
更にbindValue()の第一引数を数字ではなく":hoge"":fuga"と指定します。

サンプルコード

    $sql = "insert into phptodo (name, done, priority) values (:name, 0, :priority)";  
    $stmt = $dbh->prepare($sql);

    $stmt->bindValue(":name", $name, PDO::PARAM_STR);
    $stmt->bindValue(":priority", $priority, PDO::PARAM_STR);
    $stmt->execute();

参考

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む