GMOさんのこちらの記事を試した上で、もう少しデータを増やしたものです。 https://techblog.gmo-ap.jp/2020/01/06/mysql-innodb-fulltext-search-tuto/ 全文検索とは 長い文章に含まれる単語ごとの出現回数リストを用意することで、SQLのlike文より柔軟にほしい文書を早く検索する機能です。現実の用途としてはGoogleなど検索エンジンが代表的なもので、Amazonで何を検索しても商品名から解説文までマッチして一瞬で結果一覧が出るのも、Macのファイルやアプリを探すSpotlight検索やそのCLI版であるmdfind命令も似たような仕組みで動いていると思われます。 以前はMySQLに専用製品を組み合わせたり、ElasticSearchなどの別製品で扱われることが多かったのですが、2015年のMySQL5.7から標準で日本語の全文検索がつきました。 しくみ 文章を分解して、単語ごとの出現回数リスト(Index=もくじ)を作り、本文でなくそのインデックスに問い合わせることで、通常のlike文などの直接全テキストを探す方法よりも、ほしい文書を早く柔軟に見つけ重要な順に表示します。 登録時に時間をかけてインデックス化して整理することで、問い合わせの応答を早くするという仕組みなので、あまり更新されない大量のデータ(50年分の新聞記事、100年分の裁判例)や、更新されるけれど問い合わせの方がはるかに多いサービス（数億サイトを扱うWeb検索エンジン、マッチングサイト200万人のプロフィール検索）に向いています。 日本語と英語 単語出現のリストを作るにあたって、英語などの単語を空白で区切る言語ではリスト作成が簡単で、日本語や中国語は単語の区切りがないぶん工夫が必要で、機械的に数文字ごとにリスト化するN-gramや、単語辞書と文法を用意した上でリスト化する形態素解析（文章を最小要素に分解すること）などの手法があります。 歴史 もともと2000年ごろにGoogleなど検索エンジン周辺の必要からオープンソースで開発が進み、それが事業化する形でElastic社のElasticSearchなどが出てきて、MySQLも日本語全文検索できるよう改造したものが売られたり（ひろゆきさんの未来検索ブラジルという会社）しましたが、2015年のMySQL5.7で公式に日本語全文検索がつきました。 InnoDBのテーブル作る MySQLで日本語全文検索をつけられるストレージエンジンはInnoDBです。 TEXT型のbody（本文）という列に日本語全文検索をつけます。 全文検索の仕組みはインデックスなので列のデータ型はVARCHARでもTEXTでも構いません。 CREATE TABLE full_text_test ( id int AUTO_INCREMENT NOT NULL PRIMARY KEY, body TEXT ) ENGINE=InnoDB CHARACTER SET utf8mb4; データつくる 東京や大阪や京都といった単語が一回あるいは大量に使われた文章を登録します。 INSERT INTO full_text_test (body) VALUES ('東京都で遊ぶ'), ('京都で遊ぶ'), ('大阪で遊ぶ'), ('京都と大阪で遊ぶ'), ('東京都で働く'), ('京都で働く'), ('大阪で働く'), ('京都と大阪で働く'), ('京都と大阪で遊ぶけど、やっぱり京都が好きで5回京都に行ったし京都に住んだこともあるし、働いたこともある'), ('東京都で遊ぶといえば、東京駅から初めて東京都庁をめぐり・・・。'), ('大阪大好き！ 大阪大阪大阪大阪大阪大阪大阪大阪！！ 大阪大阪大阪大阪大阪大阪大阪大阪！！'), ('東京大好き！ 東京東京東京東京東京東京東京！！ 東京東京東京東京東京東京東京！！'), ('東京大好き！ 東京東京東京東京東京東京東京！！ 遊びまくる、遊ぶ遊ぶ遊ぶ遊ぶ遊ぶ！'), ('東京大好き！ 東京東京東京東京東京東京東京！！ 働きまくる！ 働く働く働く働く働く働く！'); フルテキスト（全文検索）インデックスをはる n-gram方式のインデックスをbody列につけます。 ALTER TABLE full_text_test ADD FULLTEXT INDEX ngram_idx (body) WITH PARSER ngram; 取り出してみる boolean mode SELECT * FROM full_text_test WHERE MATCH (body) AGAINST ('東京' IN BOOLEAN MODE); // 東京と関係が深い順 SELECT * FROM full_text_test WHERE MATCH (body) AGAINST ('大阪' IN BOOLEAN MODE); // 大阪と関係が深い順 SELECT * FROM full_text_test WHERE MATCH (body) AGAINST ('京都' IN BOOLEAN MODE); // 京都と関係が深い順 SELECT * FROM full_text_test WHERE MATCH (body) AGAINST ('労働' IN BOOLEAN MODE); // 労働と関係が深い順 SELECT * FROM full_text_test WHERE MATCH (body) AGAINST ('+東京 都庁 ' IN BOOLEAN MODE); // 東京があって、都庁もあればなお良い SELECT * FROM full_text_test WHERE MATCH (body) AGAINST ('+京都 -東京' IN BOOLEAN MODE); // 京都があって東京がないものを重要度順に SELECT * FROM full_text_test WHERE MATCH (body) AGAINST ('+京都 +大阪 -東京' IN BOOLEAN MODE); SELECT * FROM full_text_test WHERE MATCH (body) AGAINST ('+京都 大阪 -東京' IN BOOLEAN MODE);

GMOさんのこちらの記事を試した上で、もう少しデータを増やしたものです。 https://techblog.gmo-ap.jp/2020/01/06/mysql-innodb-fulltext-search-tuto/ 全文検索とは SQLのlike文より柔軟にほしいデータを検索する機能。 前はMySQLに専用製品を組み合わせたり、ElasticSearchなどの別製品で扱われることが多かったのですが、2015年のMySQL5.7から標準で日本語の全文検索がつきました。 テーブル作る CREATE TABLE full_text_test ( id int AUTO_INCREMENT NOT NULL PRIMARY KEY, body TEXT ) ENGINE=InnoDB CHARACTER SET utf8mb4; データつくる INSERT INTO full_text_test (body) VALUES ('東京都で遊ぶ'), ('京都で遊ぶ'), ('大阪で遊ぶ'), ('京都と大阪で遊ぶ'), ('東京都で働く'), ('京都で働く'), ('大阪で働く'), ('京都と大阪で働く'), ('京都と大阪で遊ぶけど、やっぱり京都が好きで5回京都に行ったし京都に住んだこともあるし、働いたこともある'), ('東京都で遊ぶといえば、東京駅から初めて東京都庁をめぐり・・・。'), ('大阪大好き！ 大阪大阪大阪大阪大阪大阪大阪大阪！！ 大阪大阪大阪大阪大阪大阪大阪大阪！！'), ('東京大好き！ 東京東京東京東京東京東京東京！！ 東京東京東京東京東京東京東京！！'), ('東京大好き！ 東京東京東京東京東京東京東京！！ 遊びまくる、遊ぶ遊ぶ遊ぶ遊ぶ遊ぶ！'), ('東京大好き！ 東京東京東京東京東京東京東京！！ 働きまくる！ 働く働く働く働く働く働く！'); フルテキスト（全文検索）インデックスをはる ALTER TABLE full_text_test ADD FULLTEXT INDEX ngram_idx (body) WITH PARSER ngram; 取り出してみる boolean mode SELECT * FROM full_text_test WHERE MATCH (body) AGAINST ('東京' IN BOOLEAN MODE); // 東京と関係が深い順 SELECT * FROM full_text_test WHERE MATCH (body) AGAINST ('大阪' IN BOOLEAN MODE); // 大阪と関係が深い順 SELECT * FROM full_text_test WHERE MATCH (body) AGAINST ('京都' IN BOOLEAN MODE); // 京都と関係が深い順 SELECT * FROM full_text_test WHERE MATCH (body) AGAINST ('労働' IN BOOLEAN MODE); // 労働と関係が深い順 SELECT * FROM full_text_test WHERE MATCH (body) AGAINST ('+東京 都庁 ' IN BOOLEAN MODE); // 東京があって、都庁もあればなお良い SELECT * FROM full_text_test WHERE MATCH (body) AGAINST ('+京都 -東京' IN BOOLEAN MODE); // 京都があって東京がないものを重要度順に SELECT * FROM full_text_test WHERE MATCH (body) AGAINST ('+京都 +大阪 -東京' IN BOOLEAN MODE); SELECT * FROM full_text_test WHERE MATCH (body) AGAINST ('+京都 大阪 -東京' IN BOOLEAN MODE);

MySQLでデータ取得する時はこうSQLを書くけど、DjangoのQuerySetを取得する時はどうするんだろうと思うことがあり、いろいろ試してみました。 環境 Django 2.2.3 Python 3.7.4 内部結合したテーブルのpk以外で検索したい modelsはそれぞれコードと名前のフィールドを持ち、市テーブルに都道府県テーブルと紐づけるForeignKeyを設定しています。 紐づけるフィールドはpk（プライマリーキー）にするので、to_field等は指定していません。 models.py from django.db import models class Pref(models.Model): pref_cd = models.IntegerField(null=False, unique=True) pref_name = models.CharField(max_length=255, null=False) class Meta: db_table = 'pref' class City(models.Model): pref = models.ForeignKey( Pref, on_delete=models.SET_NULL, null=True ) city_cd = models.IntegerField(null=False) city_name = models.TextField(max_length=255, null=False) class Meta: db_table = 'city' unique_together = (('pref', 'city_cd')) SQL SELECT * FROM city INNER JOIN pref ON pref.id = city.pref_id WHERE pref.pref_name = '{都道府県名}'; QuerySet <ForeignKeyを設定したフィールド名>__<検索したいフィールド名>で検索できます。 City.objects.filter(pref__pref_name='{都道府県名}') 内部結合したテーブルのpk以外でソートしたい SQL SELECT * FROM city INNER JOIN pref ON pref.id = city.pref_id ORDER BY pref.pref_cd DESC; QuerySet 検索と同じように<ForeignKeyを設定したフィールド名>__<ソートしたいフィールド名>でソートできます。 City.objects.order_by('pref__pref_cd').reverse().all() ForeignKeyを設定していないテーブル同士で結合して検索したい ForeignKeyは設定せず、市テーブルにも都道府県名を持つようにしています。 models.py from django.db import models class Pref(models.Model): pref_cd = models.IntegerField(null=False, unique=True) pref_name = models.CharField(max_length=255, null=False) class Meta: db_table = 'pref' class City(models.Model): city_cd = models.IntegerField(null=False) city_name = models.TextField(max_length=255, null=False) pref_name = models.TextField(max_length=255, null=False) class Meta: db_table = 'city' SQL SELECT city_name FROM city INNER JOIN pref ON pref.pref_name = city.pref_name WHERE pref.pref_cd = {都道府県コード}; QuerySet City.objects.extra( tables=['pref'], where=['city.pref_name=pref.pref_name'] ).extra(where=['pref.pref_cd={都道府県コード}']).all() このクエリを出力してみるとわかりますが、 SQL的にはWHERE ((city.pref_name=pref.pref_name) AND (pref.pref_cd={都道府県コード})) と結合はしていません。 ほしいデータが取得できたからよしとしましたが、prefテーブルのデータは取得できないので注意です。

この記事はナイル Advent Calendar 2021 2日目の記事です。 はじめに サービスを運営していると本番で発生したバグの調査や機能開発時の動作確認などの際に本番データを使って行えるとなにかと作業がはかどります。 ですが、ユーザーの個人情報やセンシティブなデータをそのまま持ってきて、社内のメンバーが比較的カジュアルにアクセス出来る環境に反映することはセキュリティ的に非常にリスキーであり避けなければなりません。 そこで、本番データのセンシティブな部分をマスキングすることで、実際の本番に近いデータで調査・動作確認が出来るようにします。 マスキングにあたっての要件 個人が特定できない程度に非特定化加工する （個人ではなく）ユーザーを特定できるようにしたい プロダクトの動作画面上で表示される情報からユーザーの当たりが付けられるくらいにはしたい 具体的方法 メールアドレス 原型は残さず、ユーザー特定がしやすいようにユーザーIDを含める形に加工する ユーザー名 本名が入っているユーザーも居るので最初の1文字目以降は全てマスキングする ユーザーアイコン画像path 個人の肖像画像が登録されている場合があるのでダミー画像のpathに差し替える マスキングについて 環境 Aurora MySQL(5.6) 実装方針 今回はSQLだけで実現したいのでMySQLの文字列関数を使います。 MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.5 文字列関数 マスキング対象データ 本番DBをそのまま掲載できないので、今回はちょっとアレンジを加えた架空の会員情報テーブルを使います。 登録データは個人情報テストデータジェネレーターを利用させていただきました。 マスキングするSQL 実際の会員情報テーブルをマスキングするSQLはもっと複雑ですが、エッセンスだけ抽出するとこのような形です。 UPDATE users SET name = CONCAT(LEFT(name,1), REPEAT('X', CHAR_LENGTH(name) - 1)), mail = CONCAT('develop+user_',id,'@example.com'), icon_path = '/profile/default.jpg'; マスキング結果 nameの２文字目以降がすべてXでマスキングされています。 mailのメールアドレスがdevelop+user_{ユーザーID}@example.comの体系になっていてメールアドレスからどのユーザーであるかわかるようになっています。1 icon_pathは単純にデフォルトアイコン画像default.jpgに差し替えただけ。 おわりに 今回は本番データを使って調査・検証する際に個人情報のみマスキングして手っ取り早く安全性を確保する方法を提示しました。 これが必要な際に毎回手作業でやるのも手間なので、シェルスクリプトを作っておいて実行するだけでダンプとマスキングが実行されるようにしておくと便利かと思います。 一方で、個人情報データそのものに起因するバグやお問い合わせの調査をする場合はやはり本番データを参照する必要があります。その際は本番データを参照できる権限を持った限られたメンバーのみで対応するなどでセキュリティ性を担保するように気をつけましょう。 Gmailの業務ごとにメールアドレスを作成する機能を活用してdevelop@example.comのようなメーリングリストを作ればどのユーザー宛にメールを送信してもそこで受信できるのでテスト時に重宝します。 ↩

はじめに 本記事は Oracle Cloud Infrastructure Advent Calendar 2021 の1日目です。 この記事で伝えたいこと OCI(Oracle Cloud Infrastructure)上で提供しているOAC(Oracle Analytics Cloud)というBIサービスを使って、HeatWave(※)上のデータを分析できるハンズオンを作成しました。HeatWaveやOACに興味がある方は是非試して下さい！！ハンズオンの解説動画も公開しています！！ ※MySQLのマネージドサービスであるMySQL Database Service専用のクエリーアクセラレーター。インメモリカラムナーデータベースで、超並列処理により高速に参照処理を実行できる。 本文 私のGithubでHeatWaveのハンズオンを公開しています。 Lab00～Lab07までを実行することで、HeatWaveを構成し、TPC-Hのサンプルデータをロードして、HeatWaveが無効な状態/有効な状態でのパフォーマンスを比較できます。 更にLab08では、小売業のサンプルデータをHeatWaveにロードし、OAC(Orale Analytics Cloud)というBIサービスからHeatWaveに接続して様々なデータ分析を試せる内容になっています。 ここまでの内容について解説した動画もこちらで公開しているので、HeatWaveやOACに興味がある方は是非試して下さい！！