カテゴリーアーカイブ SQL

村上 著者:村上

【MySQL】データが重複した行を除いて抽出する方法

今回は、MySQLのデータ抽出についてです。
今まで使ったことがなかったので知らなかったのですが、抽出したデータの重複している行を除いてデータを抽出することができるそうなので、こちらの方法について、主に自分の備忘録のためにまとめ。

参考にさせていただいた記事はこちら。

重複行を除外(All, DISTINCT) – データの取得 – MySQLの使い方
https://www.dbonline.jp/mysql/select/index13.html

 

早速、その方法ですが、重複しているかどうかを指定したいカラムの前に「DISTINCT」を付けるだけです。
具体的には下記のように指定します。

SELECT DISTINCT [カラム名] FROM [テーブル名];

なお、カラムは複数指定することもできます。
その場合は、下記のように指定します。

SELECT DISTINCT [カラム名1], [カラム名2], [カラム名3] FROM [テーブル名];

ちなみに、DISTINCT ではなく ALL と指定すると、重複した行もすべて抽出するできるとのことでしたが、これは指定しなかった場合と同じ動作なので、使うことはないと思います。

 

以上、MySQLで重複した行を除いてデータを抽出する方法でした。
今までなぜ使う機会がなかったのか疑問なくらい便利なオプションでした…。
今後は是非積極的に使っていきたいと思います!

  • この記事いいね! (0)
著者:杉浦

SQLを試すwebサービスSQL Fiddle

 SQL Fiddleは様々なSQLを簡単に試せるオンラインサービスです。下図の様な画面で、任意のSQL環境を選び、テーブルを定義し、SQLを実行する、といったものです。
 
 左の赤丸は実行環境、右の赤丸はテキストからData Definition Languageへの変換機能です。下図の様に、ヘッダ、データを用意するとテーブル作成とそのテーブルにデータを入れる構文が出力されます。
 
 この図では半角スペースで区切っていますが、様々な区切り方に対応しています。詳しくはDDLBuilder/fixture.html at master · jakefeasel/DDLBuilder · GitHubを参照です。入力したデータはBrowserボタンから開くと見やすいです。
 データを入力したら、任意のSQLを右側に入れて実行、下側に出力される結果を見るだけです。

  • この記事いいね! (1)
著者:杉浦

高速化のために一度の小さな問い合わせで済むSQL文を作る

 プログラムを扱っているとDB(データベース)に接続する必要のある場面がいくつもあります。DBへのアクセスにかかる時間、実行にかかる時間は無視出来ず、実行速度のボトルネックになることがあります。DBの内部をチューニングする事でも速度の向上はできますが、問い合わせの回数、DBから引っ張ってくるデータの大きさといった部分を問い合わせ文の工夫によって少なく、小さくすることによっても高速化はできます。
 よくある場面は、必要なデータが複数のテーブルに分散している、あるテーブル中に格納されたデータを基にした条件に合致するデータのみを取得したい、といったものです。こういった時、それぞれのテーブルをそのまま扱うようなことをすると次の様なコードが生まれます。

$arr = query('SELECT * FROM master_table');
foreach($arr as $v){
	$hoge_addr = query("SELECT * FROM hoge_table WHERE addr = '${arr['addr']}'");
	...
	...
}

 こういった何度も問い合わせをDBに送る様なコードは実行にかかる時間が長くなりがちです。DBへの問い合わせは少ない回数で少ないデータをとれるようにした方がプログラムは高速になります。必要最小限のデータの取得は*を使わずに細かく指定することでできます。問い合わせを少ない回数で行うためにはAS、JOIN、入れ子内部のSELECTが便利です。

SELECT * FROM(
	SELECT
		master_table.id,
		master_table.name,
		hoge_addr.name AS name1,
		hoge_addr2.name AS name2,
		fuga_table.name
	FROM
		master_table
		INNER JOIN
			hoge_table AS hoge_addr
		ON  master_table.addr = hoge_addr.addr
		INNER JOIN
			hoge_table AS hoge_addr2
		ON  master_table.addr2 = hoge_addr.addr
		INNER JOIN
			fuga_table
		ON  master_table.branch = fuga_table.id
) AS tmptable
WHERE
	name1 = 'hogehoge'

 SELECTの結果はそのままテーブルとして扱うことが出来ます。ここではそうまとめの様に扱っていますがWHERE句中に使うこともできます。

WHERE id IN (SELECT hoge_id FROM hoge_table WHERE hogehoge = 'fugafuga')

とすればINにより()内の結果であるhoge_idのいずれかと一致するIDという条件を作れます。
 JOINは表の結合です。オプションは様々でINNER以外も多く使われます。ASはそのままA as BでAをBとして扱うという句です。JOINとASを組み合わせることで同じ表から違う条件で汲み上げた結果を一つの表にまとめられます。

master_table
INNER JOIN
	hoge_table AS hoge_addr
ON  master_table.addr = hoge_addr.addr
INNER JOIN
	hoge_table AS hoge_addr2
ON  master_table.addr2 = hoge_addr.addr
  • この記事いいね! (0)
村上 著者:村上

【MySQL】テーブルのデータのみをダンプする方法

少し前に、データベースのテーブルの構成情報のみを mysqldumpコマンドでエクスポートする方法についてまとめましたが、今回はデータのみを insert文で抽出する方法について。
めったに使わないので、なかなかオプションが覚えられず…。
なので、あとあと自分がコピー&ペーストして使えるようにまとめます。

なお、前回の記事はこちらから。

【MySQL】テーブルの情報をエクスポート・インポートする方法
https://cpoint-lab.co.jp/article/201808/【mysql】テーブルの情報をエクスポート・インポー/

 

さて、データのみを抽出するコマンドは下記のとおりです。

mysqldump -u [ユーザー名] -p -t [データベース名] [テーブル名] > [エクスポートするファイル名]

-t のオプションがポイントで、こちらがあるとテーブル構成情報を除いて、データのみをエクスポートできます。
なお、-t ではなく –no-create-info でも同じことができます。
が、個人的には、短い -t オプションの方が使いやすいかなと思います。

 

以上、データベースのテーブルからデータのみを抽出する方法でした。
本当は、MySQLの INTO 構文を使ってデータをCSVで抽出したかったのですが、どうやらそれを実行するための権限がなかったのでこのような方法をとることになりました。
ほんの少しデータを整形する必要はありましたが、こちらの方法でも簡単にデータ抽出を行えましたので、場合によってはこちらの方法を使ってもいいのかもしれません。

  • この記事いいね! (0)
takahashi 著者:takahashi

PostgreSQLで大文字のカラム名を指定する方法

一番最初に触ったDBがMySQLだったのですが、それに慣れてしまったがゆえに、他のDBエンジンを触った時にその作法の違いに苦労することがあります。

今回、PostgreSQLを使用したシステムの補修を行ったのですが、例えばSelect文を書く時、MySQLを触っていた時の癖で

SELECT * FROM table WHERE column="value";

のように書いていたのですが、PostgreSQLだとエラーになってしまいます。
というのも、PostgreSQLでは “” は文字列を意味するのではなく、カラム名などを大文字を表すときに使うためです。

では文字列を囲うようにするにはどうすればいいかというと

SELECT * FROM table WHERE column='value';

とシングルコートを用いればいいようです。

基本的にSQLを使ったDBでは、SQL文はほぼ同様に使えるのですが、細かい”作法”が結構異なっているものも多いので注意が必要です。

  • この記事いいね! (0)
takahashi 著者:takahashi

PostgreSQL “対向(peer)認証に失敗しました” エラーが出るときの対処法

PostgreSQLでちゃんとユーザーにログイン権限とパスワードを設定しているのに、

psql: FATAL:  ユーザ "postgres" で対向(peer)認証に失敗しました

のようなエラーが出てしまった場合の対処法です。

そもそもPeer認証とは

CentOS6でPostgreSQLインストール – My Octopress Blog

Peer認証とは、カーネルからクライアント上のシステムユーザ名を取得し、PostgreSQLデータベースユーザと同一である場合のみ接続が許可される仕組みです。

つまり、Postgresql内のユーザーとUNIXユーザで、ユーザー名が一致してさえいれば認証情報なしでログイン出来てしまう仕組みです。

パスワードを打たなくてもいいのは楽ですが、例えばユーザーごとに権限を変えて置いて、場合によって使い分けたいときや一時的に別のpsqlユーザーとしてログインしたいときなどはかなりやりづらいですし、psql側でユーザーを作る際に、同名のUNIXユーザーも追加する必要が出てくるのでとてもめんどくさいです。

この挙動を変更するには、pg_hba.conf (だいたいpsqlのデータフォルダか/etcにあるはずです。)を編集します。
ファイルを開くと

#ローカルで動いているpsqlへアクセスする場合
local   all             all                                     peer
#他のクライアントからpsqlへアクセス可能にする場合(例)
host    all             all             127.0.0.1/32            peer

などとなっているか、あるいはコメントアウトされているかと思いますので、これを書き換えます。

#ローカルで動いているpsqlへアクセスする場合
local   all             all                                     md5
#他のクライアントからpsqlへアクセス可能にする場合(例)
host    all             all             127.0.0.1/32            md5

パスワード認証させたい場合は”peer”の部分を”md5″に変更して保存し、psqlのデーモンを再起動させます。

これで再度ログインを行えば、今度はパスワードを求められ、正しい情報を入力すればどのユーザーでもログインできるようになるかと思います。

  • この記事いいね! (0)
takahashi 著者:takahashi

MariaDBでDBをテーブル単位で暗号化する方法

最近、何かと情報流出のニュースって多いですよね。
基本的にはサーバに不正侵入させないのが第一ではあるのですが、攻撃が巧妙化してくると、なかなか簡単には防御できないこともあり得ます。
そういう場合も想定して、万が一サーバに第三者が不正に侵入してきたときの保険として、データそのものを暗号化してしまう方法があります。

Webサービスなどでは、データの保存先としてよくデータベースを利用しますが、最近このDBエンジン自体が暗号化機能を持っているものが出てきました。

今回はMariaDBというMySQL互換のDBエンジンで暗号化設定をしてみました。
(使用OS:CentOS 7)

まずはmariaDBをインストールします。
mariaDB自体はCentOSのepelリポジトリにも存在しているのですが、実はepel版MariaDBはバージョン5系のものとなっており、暗号化がサポートされるのが10.1からとなっています。
そのため、公式リポジトリのMariaDBでは暗号化することができません。

バージョン10.1以上のMariaDBはMariaDB公式のリポジトリから入手できるので、今回はこちらを使います。

折角なら最新版をということで、現時点の安定板の最新版 v10.3 をインストールします。

sudo vi /etc/yum.repos.d/mariadb.repo

などどして、下記の内容でMariadbのリポジトリファイルを作成します。

# MariaDB 10.3 CentOS repository list - created 2018-07-04 11:06 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

ファイルを作成したらyumでmariadbをインストールします。

#MariaDBをインストール
sudo yum install MariaDB-server MariaDB-client

MariaDBを立ち上げる前に、暗号化の設定を先に行っておきます。
まずは暗号化に使うキーを生成します。

openssl enc -aes-256-cbc -k パスワード -P -md sha1

下記のような内容が表示されます。

salt=英数字
key=英数字
iv =英数字

新しくファイルを作成し、表示された英数字を次のように記載します。

暗号化・復号化する際の鍵番号(1以上の数字を任意に指定);ivの値;keyの値

鍵ファイルを暗号化します。

openssl enc -aes-256-cbc -md sha1 -k パスワード2 -in 先程つくった鍵ファイルのパス -out 暗号化後ファイルの保存先

/etc/my.cnf.d/server.cnf
を管理者権限で開き、[mysqld]セクションのすぐ下に、下記の設定を追記します。

plugin-load-add=file_key_management.so
file_key_management
file_key_management_filename = 先程作成した鍵ファイルのパス
file_key_management_filekey = 鍵ファイルにかけたパスワード
file_key_management_encryption_algorithm=AES_CBC

これで準備ができたので、早速MariaDBを立ち上げます。

sudo systemctl start mariadb #mariadb起動
sudo systemctl enable mariadb #mariadbの自動起動を有効化

これで暗号化が有効になったはずです。
それでは早速、暗号化されたテーブルを作ってみます。

CREATE DATABASE hoge;
use hoge;
CREATE TABLE test (id INT) ENCRYPTED=YES ENCRYPTION_KEY_ID=先程の鍵に指定した鍵id;

テーブルにデータを入れます。

#例
INSERT INTO hoge(id) VALUES (1);

この状態でselectをかけると通常通りデータが出力されますが、最初に編集した
/etc/my.cnf.d/server.cnf
の今回の変更部分をすべてコメントアウトした状態でmariaDBを再起動、再びhogeテーブルをSELECTすると…

ERROR 1932 (42S02): Table 'hoge.test' doesn't exist in engine

のように表示され、中のデータが見れなくなっていることがわかるかと思います。

このようにしておけば、MariaDBのデータ本体を抜かれても、キーファイルが漏れていても、そのパスワードが漏れなければデータの中身を見られる心配はありません。
ただし、/etc/my.cnf.d/server.cnfにパスワードを直書きしてしまっているので、このファイルを漏れないように気を配る必要はありそうです。

今回のMariaDBのように、暗号化する仕組みをはじめから備えていて、簡単に設定できる環境も増えてきています。
これからもし情報が洩れてはいけないようなサービスを作ろうと考えられている方がいましたら、是非参考になれば幸いです。

  • この記事いいね! (0)