- 投稿日:2020-09-08T23:34:00+09:00
【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側の操作
後は今まで得た情報を
.env
とdatabase.php
に反映するだけ
ここでは.env
だけ書いておく.envDB_CONNECTION=mysql DB_HOST=IPアドレス DB_PORT=3306 DB_DATABASE=DB名 DB_USERNAME=作成したユーザ名 DB_PASSWORD=パスワード参考
- 投稿日:2020-09-08T23:00:29+09:00
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();かんたんですね。
- 投稿日:2020-09-08T22:33:17+09:00
Gormを使ってみた(ORMライブラリでMySQL接続)
概要
- GoでMySQL接続をしようと思っていた時に、「gorm」というORMライブラリを見つけたので使ってみた
- 公式のドキュメントを参考に実装時に必要になった知識をまとめていきます
準備
gormをインストールします
またMySQLと接続するためのDriverもインストールしますgo get -u gorm.io/gorm go get -u gorm.io/driver/mysqlDB接続
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}]主キー検索
Find
やFirst
の第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が付与されます。条件指定
Where
やOr
メソッドを使うことでクエリを表現できます。
それぞれ
- 第一引数:検索条件
- 第二引数以降:バインドする値
というように指定します。
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
の戻り値を取得し、RowsAffected
・Error
で確認できますresult := connection.Model(&user).Update("name", "User-X") fmt.Println(result.RowsAffected) // 1 fmt.Println(result.Error) // <nil> ※エラーなしのためNilDelete
以下のようなテーブルの状態でサンプルを実装・実行していきます
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 })
- 投稿日:2020-09-08T21:42:58+09:00
小ネタ/MySQL 8.0 のダブルライトバッファがどのバージョンで暗号化されるようになったのか調べた
以前、MySQL 8.0 ではテーブルスペースや REDO ログ/UNDO ログ・システムテーブルを暗号化しても、ダブルライトバッファには対象データが平文で書き出されていました。
先日、MySQL 関連のオンラインイベントの Q&A で、
あれ、ダブルライトバッファってGA後に実験したときに平文で書かれちゃってたけど、それはもう直ったのか…あらためて試さないと(Q&Aに質問入れたのわたしじゃないけど)。#mysql_jp
— hmatsu47(まつ) (@hmatsu47) August 27, 2020という話を聞いたので、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に投稿したMySQL 8.0関連記事
- MySQL 8.0 の薄い本(無料で配布中!)
- 投稿日:2020-09-08T17:26:16+09:00
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();参考