- 投稿日：2020-01-22T20:10:11+09:00
よく使いそうなPHPでのデータベースへのアクセス
引用
ドットインストール PHPデータベース入門
自分用にメモしてます。DBのセットアップ
最初のセットアップmysql -u root create database new_db; grant all on new_db.* to admin@localhost identified by 'password'; use dotinstall_db; create table users ( id int not null auto_increment primary key, name varchar(255), score int ); mysql -u admin -p passwordPDOのセットアップ
DBの基本情報を定数で定義
define('DB_DATABASE', 'new_db'); define('DB_USERNAME', 'dbuser'); define('DB_PASSWORD', 'password'); define('PDO_DSN', 'mysql:host=localhost;dbname=' . DB_DATABASE);PDOは基本的にtryとcatchで記述
エラーが出た時にはExceptionを発生させるため、setAttributeというメソッドで設定する。
PHPで定義済みのクラスを書くときは先頭に「\」を付ける習慣をつける。(※)
※名前空間を使っている場合、PHPに標準で用意されているException()やDateTime()のようなクラスに関しては一番上の名前空間から呼び出さなければならない。try { // connect $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (\PDOException $e) { echo $e->getMessage(); exit; }exec()
- 引数に与えられたSQL文を実行する。
- 返り値は更新・削除された行数を返す。作用しなかった場合は0を返す。
- SELECT文からは結果を返さない
// insert $db->exec("insert into users (name, score) values ('yamada', 28)"); echo "success!"; // disconnect $db = null;prepare() execute()
prepare()
- 複数回実行されるSQL、安全対策が必要なSQL文に適している。
- DBが正常に文を準備する場合は、PDOStatementオブジェクトを返す。もしDBが文を準備できなかった場合はFALSEかPDOExceptionを発行する(エラー処理の方法に依存)
execute()
- 配列型でパラメータを渡す。
- 何も指定しない場合は文字列で渡される。数値を渡したい場合はbindvalue,bindparam 型を指定する必要がある。
$stmt = $db->prepare("insert into users (name, score) values (?, ?)"); $stmt->execute(['taguchi', 44]); echo "inserted: " . $db->lastInsertId();名前つきパラメータ
扱うカラムが多くなってきたときに整理しやすくなる。
$stmt = $db->prepare("insert into users (name, score) values (:name, :score)"); $stmt->execute([':name'=>'hoge', ':score'=>80]); echo "inserted: " . $db->lastInsertId();bindvalue()
- 第一引数:パラメータid(1から始まる)
- 第二引数:バインドする値
- 第三引数:データの型
- 文字列:PDO::PARAM_STR
- 数値は:PDO::PARAM_INT
- これを使うとループなどで一部の値だけ違うレコードを一気に挿入できる。
- 名前付きパラメータの場合にも適用できる。
$name = 'yamada'; $stmt->bindValue(1, $name, PDO::PARAM_STR); // $stmt->bindValue(':name', $name, PDO::PARAM_STR); $score = 35; $stmt->bindValue(2, $score, PDO::PARAM_INT); $stmt->execute(); $score = 29; $stmt->bindValue(2, $score, PDO::PARAM_INT); $stmt->execute();bindParam()
- bindvalueは値をバインド、bindParamは変数への参照をバインド
- 実際に変数を参照するのはexecute()時。
$stmt->bindParam(2, $score, PDO::PARAM_INT); $score = 52; $stmt->execute(); $score = 44; $stmt->execute(); $score = 6; $stmt->execute();query() fetchAll() rowCount()
query()
- 何回も実行されないSQLに適する。
返り値はPDOStatementオブジェクトを返す。失敗した場合はFALSEを返す。
fetchAll()引数に返り値の形式を指定できる。
rowCount()
- 取得したレコードの数を取得
// select all $stmt = $db->query("select * from users"); $users = $stmt->fetchAll(PDO::FETCH_ASSOC); foreach ($users as $user) { var_dump($user); } echo $stmt->rowCount() . " records found.";transaction
複数の処理により整合性を保つ必要がある場合に必要。
try { // transaction $db->beginTransaction(); //処理1 //処理2 $db->commit(); } catch (\PDOException $e) { $db->rollback(); echo $e->getMessage(); exit; }
- 投稿日：2020-01-22T17:41:38+09:00
外部からGCE上のDockerコンテナ内のMySQLへ接続
やりたいこと
GCE上にDockerを使ってWebとDBのサーバーを動作させている。
アプリケーションは、お馴染みのWordPressとする。
手元のPCから、直接MySQLにログインしたい。
現状
手元のPCからMySQLにログインするには、以下の3ステップが必要。
- GCEへSSHログイン
- DB Serverとして起動しているDockerコンテナへログイン
- MySQLへログイン
この1~3を一発で行いたい。
1. SSHログイン
SSH鍵が未作成なら、以下コマンドで作成
(外部PC)$ ssh-keygen -t rsa -b 4096 -C "your_email@example.com"cf. お前らのSSH Keysの作り方は間違っている - Qiita
公開鍵を登録
手元のSSH鍵でサーバーへログインできるように登録する必要がある。
以下の操作を、権限のある人に依頼する。Compute Engine > Metadata > SSH Keys
で上の公開鍵を追加する。
（メールアドレスの@より前がユーザー名。）SSH接続確認
(外部PC)$ ssh -i ~/.ssh/id_rsa [username]@[IP]ログインできたらOK。
2. Dockerコンテナへログイン
まずはDockerでWordPressが起動していることを確認する。
以下はWordPress公式のdocker-compose.yml。docker-compose.ymlversion: '3.1' services: wordpress: image: wordpress restart: always ports: - 8080:80 environment: WORDPRESS_DB_HOST: db WORDPRESS_DB_USER: exampleuser WORDPRESS_DB_PASSWORD: examplepass WORDPRESS_DB_NAME: exampledb volumes: - wordpress:/var/www/html db: image: mysql:5.7 restart: always environment: MYSQL_DATABASE: exampledb MYSQL_USER: exampleuser MYSQL_PASSWORD: examplepass MYSQL_RANDOM_ROOT_PASSWORD: '1' volumes: - db:/var/lib/mysql volumes: wordpress: db:起動できた。
コンテナへのログインはこのコマンド。
(GCE)$ docker exec -it [コンテナID] bash root@[コンテナID]:/var/www/html#ログインできた。（プロンプトが切り替わってる）
(
Ctrl + Cでログアウトしておく。)3. MySQLへログイン
(GCE)$ docker exec -it [コンテナID] mysql -u exampleuser -pexamplepass mysql>cf. Docker上でMySQLにログインする - Qiita
ログインできた。
実行してみる
DB接続は、Sequel Proというツールを使っている。
接続失敗！
ログを見てみると、SSHログインできているけど、
127.0.0.1 port 3306への接続で失敗しているみたい。... debug1: Connection established. ... debug1: Authentication succeeded (publickey). ... debug1: Connection to port 53983 forwarding to 127.0.0.1 port 3306 requested. ... open failed: connect failed: Connection refused原因はPort
MySQLが動作しているのは、Dockerコンテナ内の3306番ポート。
アクセスしようとしていたのは、GCEの3306番ポート。
この２つを繋ぐ必要があった。
cf. Dockerでポートフォワーディング解説 - Qiita以下のように修正。
docker-compose.ymlversion: '3.1' services: wordpress: image: wordpress restart: always ports: - 8080:80 environment: WORDPRESS_DB_HOST: db WORDPRESS_DB_USER: exampleuser WORDPRESS_DB_PASSWORD: examplepass WORDPRESS_DB_NAME: exampledb volumes: - wordpress:/var/www/html db: image: mysql:5.7 restart: always ports: - 3306:3306 # 追加した environment: MYSQL_DATABASE: exampledb MYSQL_USER: exampleuser MYSQL_PASSWORD: examplepass MYSQL_RANDOM_ROOT_PASSWORD: '1' volumes: - db:/var/lib/mysql volumes: wordpress: db:再起動させる。
(GCE)$ docker-compose down (GCE)$ docker-compose upプロセスを表示すると、Portsの表示が変わった。
0.0.0.0:3306->3306/tcpになってる。変更前(GCE)$ docker-compose ps Name Command State Ports -------------------------------------------------------------------------------- test_db_1 docker-entrypoint.sh mysqld Up 3306/tcp, 33060/tcp test_wordpress_1 docker-entrypoint.sh apach ... Up 0.0.0.0:8080->80/tcp変更後(GCE)$ docker-compose ps Name Command State Ports --------------------------------------------------------------------------------------------- test_db_1 docker-entrypoint.sh mysqld Up 0.0.0.0:3306->3306/tcp, 33060/tcp test_wordpress_1 docker-entrypoint.sh apach ... Up 0.0.0.0:8080->80/tcp再実行
接続できた！?
- 投稿日：2020-01-22T17:41:38+09:00
外部からGCE上のDockerコンポーネント内のMySQLへ接続
やりたいこと
GCE上にDockerを使ってWebとDBのサーバーを動作させている。
アプリケーションは、お馴染みのWordPressとする。
手元のPCから、直接MySQLにログインしたい。
現状
手元のPCからMySQLにログインするには、以下の3ステップが必要。
- GCEへSSHログイン
- DB Serverとして起動しているDockerコンポーネントへログイン
- MySQLへログイン
この1~3を一発で行いたい。
1. SSHログイン
SSH鍵が未作成なら、以下コマンドで作成
(外部PC)$ ssh-keygen -t rsa -b 4096 -C "your_email@example.com"cf. https://qiita.com/suthio/items/2760e4cff0e185fe2db9
公開鍵を登録
手元のSSH鍵でサーバーへログインできるように登録する必要がある。
以下の操作を、権限のある人に依頼する。Compute Engine > Metadata > SSH Keys
で上の公開鍵を追加する。
（メールアドレスの@より前がユーザー名。）SSH接続確認
(外部PC)$ ssh -i ~/.ssh/id_rsa [username]@[IP]ログインできたらOK。
2. Dockerコンポーネントへログイン
まずはDockerでWordPressが起動していることを確認する。
以下はWordPress公式のdocker-compose.yml。docker-compose.ymlversion: '3.1' services: wordpress: image: wordpress restart: always ports: - 8080:80 environment: WORDPRESS_DB_HOST: db WORDPRESS_DB_USER: exampleuser WORDPRESS_DB_PASSWORD: examplepass WORDPRESS_DB_NAME: exampledb volumes: - wordpress:/var/www/html db: image: mysql:5.7 restart: always environment: MYSQL_DATABASE: exampledb MYSQL_USER: exampleuser MYSQL_PASSWORD: examplepass MYSQL_RANDOM_ROOT_PASSWORD: '1' volumes: - db:/var/lib/mysql volumes: wordpress: db:起動できた。
コンテナへのログインはこのコマンド。
(GCE)$ docker exec -it [コンテナID] bash root@[コンテナID]:/var/www/html#ログインできた。（プロンプトが切り替わってる）
(
Ctrl + Cでログアウトしておく。)3. MySQLへログイン
(GCE)$ docker exec -it [コンテナID] mysql -u exampleuser -pexamplepass mysql>cf. https://qiita.com/ymstshinichiro/items/01f6a4c299fdb4b832a2
ログインできた。
実行
DB接続は、Sequel Proというツールを使っています。
接続失敗！
ログを見てみると、SSHログインできているけど、
127.0.0.1 port 3306への接続で失敗しているみたい。... debug1: Connection established. ... debug1: Authentication succeeded (publickey). ... debug1: Connection to port 53983 forwarding to 127.0.0.1 port 3306 requested. ... open failed: connect failed: Connection refused原因はPort
MySQLが動作しているのは、Dockerコンポーネント内の3306番ポート。
アクセスしようとしていたのは、GCEの3306番ポート。
この２つを繋ぐ必要があった。以下のように修正。
docker-compose.ymlversion: '3.1' services: wordpress: image: wordpress restart: always ports: - 8080:80 environment: WORDPRESS_DB_HOST: db WORDPRESS_DB_USER: exampleuser WORDPRESS_DB_PASSWORD: examplepass WORDPRESS_DB_NAME: exampledb volumes: - wordpress:/var/www/html db: image: mysql:5.7 restart: always ports: - 3306:3306 # 追加した environment: MYSQL_DATABASE: exampledb MYSQL_USER: exampleuser MYSQL_PASSWORD: examplepass MYSQL_RANDOM_ROOT_PASSWORD: '1' volumes: - db:/var/lib/mysql volumes: wordpress: db:再起動させる。
(GCE)$ docker-compose down (GCE)$ docker-compose upプロセスを表示すると、Portsの表示が変わった。
0.0.0.0:3306->3306/tcpになってる。変更前(GCE)$ docker-compose ps Name Command State Ports -------------------------------------------------------------------------------- test_db_1 docker-entrypoint.sh mysqld Up 3306/tcp, 33060/tcp test_wordpress_1 docker-entrypoint.sh apach ... Up 0.0.0.0:8080->80/tcp変更後(GCE)$ docker-compose ps Name Command State Ports --------------------------------------------------------------------------------------------- test_db_1 docker-entrypoint.sh mysqld Up 0.0.0.0:3306->3306/tcp, 33060/tcp test_wordpress_1 docker-entrypoint.sh apach ... Up 0.0.0.0:8080->80/tcp再実行
接続できた！?
- 投稿日：2020-01-22T17:09:30+09:00
MySQLをMacのターミナルで操作するときのメモ
毎度てんやわんやするので整理メモを残します。
随時更新します。ご指摘・代替案等ありましたらコメントいただけますと幸いです。
環境状況
- Mac OSCatalina 10.15.2
- MySQL 8.0.19（Homebrewで
brew install mysqlして入れたもの）- ターミナルでの作業
懸念点
・/usr/local/opt配下に"mysql"と"mysql＠8.0"がある
（バージョンはどちらも同じ）
→ 以前ポート番号が0になって接続できなかったことがあり、そのときの原因はこの見る先の問題かも？と想定（mysqladmin shutdownにて解決）・何回もインストールし直している
どこかでなにやらやばいことになってないか不安知識
個人的に覚えておきたいこと
- MySQLの設定は/etc/my.cnf（参考 - Qiita）
- MySQL 8.0では、ホストサーバーにログインしないでも設定値の変更から再起動で反映までコマンドでできるようになった（参考 - スマートスタイル TECH BLOG）
- 基本的にSQLは大文字でも小文字でも大丈夫
- 小文字だとダメだったときがあるけど、それはそもそも文を間違えてた可能性もある
- Macのターミナルでコマンドが実行できない＆改行しまくれちゃう地獄に陥ったときは
control + c
/Cでも\cでもcommand + cでもなくcontrol + c- MySQL 8.0からユーザ作成と権限付与は別々でちゃんと行わないといけなくなった
- rootは元々パスワードがついていないので付けた方がいい（参考 - CodeCampus）
- MySQL内で設定を変えたら、基本的には一回出る
☆ MySQLの状態を調べる
（MySQL内から状態を見るパターンは後述）
・情報を見たいとき
$ brew info mysql
・バージョン確認
$ mysql --version
（参考 - Qiita）コマンド実行
☆ 文頭の違い
$：通常のターミナルでの操作
mysql>：MySQLにログインしてからの操作 → 文末に;がないと実行されない基本操作
MySQLの起動
$ mysql.server start
$ mysql.server start --skip-grant-tables
→ パスワードなしでログインできるMySQLにログイン（実行、接続とも）
$ mysql -u root -p「「ユーザ名：root」で接続します、パスワードは後で聞いて」という意味
らしい（抜粋元 - プロエンジニア）
このコマンドの後、パスワードが聞かれるので入力する（打ったパスワードは画面に表示されない）MySQLからログアウト
mysql> exit
quitでも\qでも可MySQLの停止（終了とも）
$ mysql.server stopバッテリーを消耗しやすいため、使わないときは停止させておいた方がいいらしい
MySQLの再起動
$ mysql.server restartMySQLの自動起動
$ brew services start mysqlHomebrewの機能（多分）
重複起動させたりしてわけわからんことになるので、
調べながらやってるビギナーなうちはやらない方がいい
事故る☆ 止めたいときは
$ brew services stop mysqlMySQL関連
どうなってるか見る
mysql> show variables like 'version'; #バージョン確認 mysql> show status; #セッション統計情報の表示 mysql> show status like "Threads_connected"; #現在の同時接続数を表示ユーザ関連
前述のログインするユーザに関連
rootだとなんでもできてしまってこわいので、
必要に応じて別途DBを利用するユーザを作っておいた方がいい現在のユーザ確認
mysql> SELECT user(); # ログイン中のユーザ表示 mysql> SHOW GRANTS; # 現在ログイン中ユーザの権限確認ユーザ一覧の表示
mysql> SELECT user, host FROM mysql.user;mysqlというデータベースにあるuserテーブルにアクセス
他にもいろんなカラムがあるユーザの作成
mysql> CREATE USER '[作成したいユーザ名]'@'[ホスト名]' IDENTIFIED BY '[指定したいパスワード]';ホスト名には、MySQLへ接続するホストを指定する
ホスト名の代わりにIPアドレスやlocalhostを指定することも可（参考 - プロエンジニア）ユーザの削除
mysql> DROP USER '[削除したいユーザ名]'@'[ホスト名]';ユーザの権限付与
mysql> GRANT [権限] ON [適用対象のデータベース].[適用対象のテーブル] TO '[権限付与するユーザ名]'@'[ホスト名]'; mysql> FLUSH PRIVILEGES; # 権限の変更をデータベースに反映＜Point 1＞
検索すると以下のような書き方がよく出てくるGRANT [権限] ON [適用対象のデータベース].[適用対象のテーブル] TO '[権限付与するユーザ名]'@'[ホスト名]' IDENTIFIED BY '[パスワード]';
が、MySQL 8.0の場合ユーザ作成と権限付与は別で行わないといけないので、文末に
IDENTIFIED BY '[パスワード]'を書いて実行すると構文エラーになる1＜Point 2＞
権限の書き方、種類、権限付与により操作を許可するDBやテーブルの書き方は、以下参考URLの該当項目を参照
MySQLでユーザを作成し、権限を設定する方法 | プロエンジニア対象ユーザの権限を確認
mysql> SHOW GRANTS FOR '[ユーザ名]'@'[ホスト名]';対象ユーザのパスワード変更
mysql> UPDATE mysql.user SET password=password('[新パスワード]') WHERE user='[対象ユーザ]'; mysql> FLUSH PRIVILEGES; # 権限変更を反映対象ユーザの権限を削除
mysql> REVOKE [権限] ON [適用対象のデータベース].[適用対象のテーブル] FROM '[権限を削除するユーザ名]'@'[ホスト名]'; mysql> FLUSH PRIVILEGES; # 権限変更を反映データベース関連
ほぼ抜粋
Macでmysqlを扱う方法 | Qiita
※自分の実行結果に修正・追記予定データベース確認
mysql> SHOW databases; #データベース一覧を表示 mysql> USE [データベース名] #使用するデータベースを選択（セミコロン不要） mysql> CREATE database [データベース名]; #データベースの作成 mysql> DROP database [データベース名]; #データベースの削除テーブル確認
mysql> SHOW tables; # テーブル一覧を表示 mysql> SHOW tables status; # テーブル一覧のステータス情報を表示 mysql> DESC [テーブル名]; # カラム一覧を表示 mysql> SHOW full columns FROM [テーブル名]; # カラム一覧を表示、Collation付き。※Collation：照合順序。MySQLでは文字コードとソート順を持っており、ソート順の部分がCollationと呼ばれている。DB単位、テーブル単位、カラム単位で設定可能である。
インデックス確認
mysql> SHOW index FROM [テーブル名]; # 特定のテーブル mysql> USE information_schema; mysql> SELECT * FROM statistics WHERE TABLE_SCHEMA = [データベース名]; # 特定のデータベースmysqladminコマンド
管理操作を実行するためのクライアント
MySQL サーバの稼動状況を確認したり、MySQL サーバを停止したりできる
（参考 - MySQL 逆引きリファレンス）MySQLにログインしていない状態で使う
→$ mysqladmin ～このひとがいつ使うなんなのかわからなくてしっちゃかめっちゃかになったりした
mysql> ～の処理とごちゃまぜに書いてあるサイトが多いので、
しっかり区別して使うようにする☆ パスワード送信オプション
-u [ユーザー名] -p← これのこと
後述のmysqladminコマンドを実行する際、
パスワードを設定しているユーザが対象である場合は文末に記述する
そうしないと権限エラーになります
（例）$ mysqladmin ping -u root -pパスワードを設定していないユーザであれば書かなくてもいける
MySQL サーバが起動中であるかを確認
$ mysqladmin pingMySQLサーバを起動していない場合は、
パスワードを設定していても$ mysqladmin pingでわかる（エラー内容から）MySQL サーバの状態を表示
$ mysqladmin status $ mysqladmin extended-status #詳細情報ユーザーのパスワードを変更
$ mysqladmin password [新パスワード] -u [ユーザー名] -p＜Point 1＞
[新パスワード]の部分はそのままパスワードを書く
いつもみたいに'pass12345'とか書くと、パスワードがpass12345ではなく'pass12345'になる
（シングルクォーテーションもパスワードになっちゃう）＜Point 2＞
コマンドの履歴を記載するログファイルに
パスワードが残ってしまうらしいので、セキュリティ的にはよろしくない
（参考 - CodeCampus）mysqladmin コマンドのヘルプを表示
$ mysqladmin -?※パスワード送信オプション不要
実行中のプロセスの一覧を表示
$ mysqladmin processlistプロセス（処理中の接続）を強制的に停止
$ mysqladmin kill [プロセスID]＜Point 1＞
プロセスID の一覧はmysqladmin processlistで調べられる＜Point 2＞
強制的に終了するプロセスを,（カンマ）区切りで複数指定できるが、
その際1, 2, 3, ...のようにカンマの後ろに半角スペースを含めるとコマンドが正しく実行されなくなる気を付けたいポイント
・「同じ処理内容に見えるけど、書き方が全然違う」ときはなんらかの違う理由があるので、適当にやらずにちゃんと違いを理解する
→ 適当にやると余計なもの同士ががっちゃんこしたりする・ターミナル系でコケるとき、基本的に「何をするコマンドなのか」をわかっていないのが原因なので、わからないことを分解して調べる
・エラーメッセージは丸々ググる前に英文を理解してから
・書いてある通りにやってできなかったらバージョンの違いを確認する
つまり、この書き方はユーザ作成と権限付与をまとめてやってるやつ ↩
- 投稿日：2020-01-22T15:11:56+09:00
mysqlのjson型のupdate、insertメモ
MySQLではバージョン5.7.8 以降でJSON型がサポートされました。
結構selectの仕方の記事は見かけるのだが実践的なupdate、insertについて書かれていることが
少なかったのでここにまとめてみます。前準備
テーブルを作っておきましょう。
create table aaa (id int, a json);id だけ入れてみると。
mysql> insert into aaa values (1,null); Query OK, 1 row affected (0.00 sec) mysql> select * from aaa; +------+------+ | id | a | +------+------+ | 1 | NULL | +------+------+ 1 row in set (0.00 sec)insert
普通のJSON
普通のjson入れるにはjson_objectを使います。
JSON_OBJECT([key, val[, key, val] ...])mysql> insert into aaa values (2, JSON_OBJECT('key1','val1', 'key2', 'val2')); Query OK, 1 row affected (0.00 sec) mysql> select * from aaa where id=2; +------+----------------------------------+ | id | a | +------+----------------------------------+ | 2 | {"key1": "val1", "key2": "val2"} | +------+----------------------------------+ 1 row in set (0.01 sec)key-valyeを奇数個にした場合はエラーが出ます。
mysql> insert into aaa values (3, JSON_OBJECT('key1','val1', 'key2', 'val2', 'aaa')); ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_OBJECT'配列
配列を入れるにはjson_arrayを使います。
数値とか文字列とか、mysqlの関数とかごちゃごちゃ入れることが出来ます。JSON_ARRAY([val[, val] ...])mysql> insert into aaa values (4, JSON_ARRAY('aaa','bbb', 1, now())); Query OK, 1 row affected (0.00 sec) mysql> select * from aaa where id=4; +------+-------------------------------------------------+ | id | a | +------+-------------------------------------------------+ | 4 | ["aaa", "bbb", 1, "2020-01-22 14:40:42.000000"] | +------+-------------------------------------------------+ 1 row in set (0.00 sec)JSON配列
JSON型の配列は配列の応用で、json_arrayの中にjson_objectを入れてあげればOK
mysql> insert into aaa values (5, JSON_ARRAY('aaa','bbb', 1, now(), JSON_OBJECT('key1','val1', 'key2', 'val2'))); Query OK, 1 row affected (0.00 sec) mysql> select * from aaa where id=5; +------+-----------------------------------------------------------------------------------+ | id | a | +------+-----------------------------------------------------------------------------------+ | 5 | ["aaa", "bbb", 1, "2020-01-22 14:42:25.000000", {"key1": "val1", "key2": "val2"}] | +------+-----------------------------------------------------------------------------------+ 1 row in set (0.00 sec)update
普通のJSON
JSONにkey-valueを追加したい場合
json_mergeを使います。
JSON_MERGE(json_doc, json_doc[, json_doc] ...)-- どちらでもよい。 update aaa set a=json_merge(a, json_object('key3', 'val3')) where id=2; update aaa set a=json_merge(a, '{"key4": "val4"}') where id=2; mysql> select * from aaa where id=2; +------+------------------------------------------------------------------+ | id | a | +------+------------------------------------------------------------------+ | 2 | {"key1": "val1", "key2": "val2", "key3": "val3", "key4": "val4"} | +------+------------------------------------------------------------------+配列のJSON
配列にデータを追加したい場合
json_array_appendを使います。
JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)pathは、$を起点に配置したい階層等を指定します。
詳しくはこちら。
今回は1階層目に入れるので$だけです。mysql> update aaa set a=json_array_append(a, '$', 'hyoo') where id=4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from aaa where id=4; +------+---------------------------------------------------------+ | id | a | +------+---------------------------------------------------------+ | 4 | ["aaa", "bbb", 1, "2020-01-22 15:00:39.000000", "hyoo"] | +------+---------------------------------------------------------+ 1 row in set (0.00 sec)pathを変えるとこんな事もできます。
mysql> update aaa set a=json_array_append(a, '$[3]', 'ttt') where id=4; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from aaa where id=4; +------+------------------------------------------------------------------+ | id | a | +------+------------------------------------------------------------------+ | 4 | ["aaa", "bbb", 1, ["2020-01-22 15:00:39.000000", "ttt"], "hyoo"] | +------+------------------------------------------------------------------+ 1 row in set (0.00 sec)元データがnullの場合
元データがnullだと、json_array_appendしてもnullのままです。がびーん
mysql> select * from aaa where id=1; +------+------+ | id | a | +------+------+ | 1 | NULL | +------+------+ 1 row in set (0.00 sec) mysql> update aaa set a=json_array_append(a, '$', 'hyo') where id=1; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> select * from aaa where id=1; +------+------+ | id | a | +------+------+ | 1 | NULL | +------+------+ 1 row in set (0.00 sec)なので、ifnullを使って回避するという荒業を思いついたので
業界のデファクトスタンダードにしましょう。mysql> select * from aaa where id=1; +------+------+ | id | a | +------+------+ | 1 | NULL | +------+------+ 1 row in set (0.00 sec) mysql> update aaa set -> a=ifnull(json_array_append(a, '$', 'hyo'), json_array('hyo')) -> where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from aaa where id=1; +------+---------+ | id | a | +------+---------+ | 1 | ["hyo"] | +------+---------+ 1 row in set (0.00 sec)配列の中にjsonが入っている場合のupdate
例えば
{"comment": "abcdefg", "created_at": now()}
というjsonを配列で保持したい場合。
json方はdefault値を持てないので必ず最初はnullになります。
なので↑のデファクトスタンダードを使ってがんばりましょう。１回目はfugafuga、2回目はmunya_munyaって文字列を入れたいよー。
-- 最初null mysql> select * from aaa where id=1; +------+------+ | id | a | +------+------+ | 1 | NULL | +------+------+ 1 row in set (0.01 sec) -- fugafugaを入れる mysql> update aaa set a=ifnull( json_array_append(a, '$', json_object('comment','munya_munya','created_at', now())), json_array(json_object('comment','hogefuga','created_at', now())) ) where id=1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from aaa where id=1; +------+-----------------------------------------------------------------------+ | id | a | +------+-----------------------------------------------------------------------+ | 1 | [{"comment": "hogefuga", "created_at": "2020-01-22 15:08:33.000000"}] | +------+-----------------------------------------------------------------------+ 1 row in set (0.00 sec) -- munyamunyaを入れる mysql> mysql> update aaa set a=ifnull( json_array_append(a, '$', json_object('comment','munya_munya','created_at', now())), json_array(json_object('comment','hogefuga','created_at', now())) ) where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from aaa where id=1; +------+-----------------------------------------------------------------------------------------------------------------------------------------------+ | id | a | +------+-----------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | [{"comment": "hogefuga", "created_at": "2020-01-22 15:08:33.000000"}, {"comment": "munya_munya", "created_at": "2020-01-22 15:08:48.000000"}] | +------+-----------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)おしまい。
- 投稿日：2020-01-22T12:08:36+09:00
MySQLのバージョン毎に実行を分けるコメント構文「!」version指定
ダンプやクエリで
/*!32302 */ や /*!50100 */ 、/*!50500 */ 、/*!50600 */とコメント構文で囲ってあるものがあります。
こちらは、「!」の後に指定しているのがバージョン番号を表していて
32302はMySQL3.23.02
50100はMySQL5.1.00
50600はMySQL5.6.00
となります。「!」文字のあとにバージョン番号を追加すると、
コメント内の構文はMySQL のバージョンが指定されたバージョン番号以上の場合にだけ実行されます。（指定されたバージョン未満は実行されません。）なので
/*!50635 select * from hoge */;はMySQL5.6.35以上のみ実行されるクエリになります。
バージョン毎に実行クエリを分けたい場合に使えます。
- 投稿日：2020-01-22T11:56:13+09:00
【C++Builder】FireDACでMySQLを使用する際の環境設定
FireDACでMySQLを使う際の環境設定にはまったので、備忘録として書く。
使用するデータベースのバージョンの選択
RAD Studioのバージョンによって、サポートしているデータベースのバージョンが違うことに注意。
C++Builderは32bitアプリケーションなので、開発環境からMySQLにアクセスするために32bit版のクライアントライブラリ（libmysql.dll）が必要になる。
さらに、自分で作成するアプリケーションが64bitである場合は64bit版のクライアントライブラリも必要になる。現在公開されているMySQLの最新は8.0.19であるが、8.0.19での32bit版クライアントライブラリの入手方法が調べてもよくわからなかったので、今回は
- RAD Studio 10.2
- MySQL 5.7.29
を使用することにした。
開発環境作成手順
MySQLのインストールに関する詳細は省略。
- MySQL64bit版インストーラーをダウンロード
- 64bitでインストールする。（インストーラは32bitだけど問題なし）
- MySQL32bit版のzipをダウンロード
3.のzipを任意のディレクトリに展開する。展開後必要なのはbinとlib配下のみなので、それ以外は削除する。
こんな感じ。FDDrivers.iniを編集し、参照するlibmysql.dllを設定する。
FDDrivers.iniの格納場所はこちらを参考に。[FDDrivers.ini] Encoding=UTF8 [MySQL] VendorLibWin32 =C:\mysql-5.7.29-win32\lib\libmysql.dll VendorLibWin64 =C:\Program Files\MySQL\MySQL Server 5.7\lib\libmysql.dllVendorLibWin32は32bit版のDLLへのパスを、VendorLibWin64は64bit版のDLLへのパスを記述する。
6.接続確認を行う。
C++BuilderでForm上にTFDConnectionを配置し、ダブルクリックする。
FireDAC接続エディタが表示されるので、作成したデータベースに関する情報を設定し、「テスト」ボタンを押す。
環境設定がうまくいっていれば下のようなポップアップが表示される。
- 投稿日：2020-01-22T05:32:43+09:00
MySQLで挿入できるレコードの文字数制限を変更する方法
ALTER文使おう
ALTER文を使ってテーブル構造の情報を変更してあげれば良い。
mysql> DESC {table_name}; +------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------------+------+-----+---------+----------------+ | id | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | title | varchar(255) | NO | | NULL | | | contents | varchar(255) | NO | | NULL | | | created_at | timestamp | YES | | NULL | | | updated_at | timestamp | YES | | NULL | | +------------+---------------------+------+-----+---------+----------------+
contentsカラムをvarchar(2000)にしたい。mysql> ALTER TABLE {table_name} MODIFY COLUMN contents varchar(1000);mysql> desc {table_name}; +------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------------+------+-----+---------+----------------+ | id | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | title | varchar(255) | NO | | NULL | | | contents | varchar(1000) | YES | | NULL | | | created_at | timestamp | YES | | NULL | | | updated_at | timestamp | YES | | NULL | | +------------+---------------------+------+-----+---------+----------------+oh yeah~♪