仮想環境でちょっとしたデータベース作業を行う際に、久しぶりにphpMyAdminを使いました。 久しぶり過ぎて、何か所か行き詰ってしまったところがあったので、既出のものばかりかもしれないけど、備忘録として投稿。 行き詰まったところ 60,000行を超えるレコード数を持った約20MBほどのCSVファイルをインポートさせると、読み込みの途中でエラーになってしまう。 SELECT DISTINCTで重複レコードをまとめるSQLを発行したにも関わらず、ダブって表示されてしまう。（大文字小文字を区別していない） 検証環境 まずは、サーバーのスペックなど。仮想ホストにはVMware ESXiを利用しています。 項目 内容 確認用のコマンド OS CentOS Linux release 7.9.2009 (Core) cat /etc/redhat-release Web Server Apache/2.4.6 (CentOS) httpd -v PHP PHP 5.4.16 (cli) (built: Apr 1 2020 04:07:17) php -v DB server mariadb-server-5.5.68-1.el7.x86_64 rpm -qa | grep -i mariadb phpMyAdmin phpMyAdmin-4.4.15.10-4.el7.noarch rpm -qa | grep -i phpmyadmin 設定ファイルを修正する さまざまなphpの設定をするphp.iniを修正。インポートファイルのサイズ制限もここで変更。 デフォルト値 /etc/php.ini upload_max_filesize = 2M post_max_size = 8M 今後のことも考えて、大幅にアップさせることにしました。 変更後 /etc/php.ini upload_max_filesize = 32M post_max_size = 128M Webサーバーを再起動させ反映させる。 # systemctl restart httpd 注意点 upload_max_filesizeだけ変更しても上限8,192KBになってしまう。 post_max_sizeも変更する。 すると32MiBに変わる。 大文字小文字の区別 調べたらところ、バイナリにすることで解決するようだ。 該当カラムの照合順序を「utf8_bin（UNICODEバイナリ）」に指定することで解決。 ※デフォルトで作ると「utf8_general_ci（UNICODE・大文字小文字を区別しない）」で作られるみたい。

セミコロンの代わりに \G をつけましょう。 SELECT * FROM mytable\G

はじめに いままでSqueak4.4とPharo7でWebアプリサーバーを構築していましたが、DBXTalkのインストールが失敗するためOracleデータベースへの接続にはperlのDBI/DBDライブラリを利用していました。 ただし、perlを呼ぶOSProcessはWindowsでは動かないためLinux版Pharoでしか開発／テストが出来ず、WindowsPCで開発してLinuxサーバーで運用というスタイルが取れないのがこれまでの悩みでした。 今回Pharo8へのマイグレーションを行う際に、データベースへのアクセスをnode.jsに代行させるようにしたらすべて解決出来たので共有します。 ついでにMySQLの接続もサポートしました。 概念図 PharoでSeaside3をWebサーバーとして起動します。 node.jsはapiサーバーとして機能します（今回はWindows10PCに立てましたがWindowsでもLinuxでも好きな環境に立てて下さい） ソースコード node.js server.jsはサーバーとしてポート3000をリッスンして起動します。 運用環境ではpm2で自動起動するといいと思います。 server.js let restify = require("restify"); let server = restify.createServer(); server.use(restify.plugins.bodyParser()); const oracledb = require("oracledb"); const mysql = require("mysql"); const util = require("util"); let pool; const init = async () => { try { await oracledb.createPool({ user: "admin", password: "pass", connectString: "oracle.example.com/example", poolMax: 4, poolMin: 0 }); console.log("Connection pool started"); pool = mysql.createPool({ host: "localhost", user: "admin", password: "pass", timezone: "jst", multipleStatements: true }); } catch (err) { console.error("init() error: " + err.message); } } const execOracle = async (sql, binds, options) => { let connection; try { connection = await oracledb.getConnection(); const result = await connection.execute(sql, binds, options); return result; } catch (err) { console.error(err); } finally { if (connection) { try { await connection.close(); } catch (err) { console.error(err); } } } } const execMysql = async (request) => { pool.query = util.promisify(pool.query); try { const result = await pool.query(request); return result; } catch (err) { throw new Error(err); } } const closePoolAndExit = async () => { console.log("\nTerminating"); try { await oracledb.getPool().close(10); console.log("Pool closed"); pool.end(); process.exit(0); } catch (err) { console.error(err.message); process.exit(1); } } (async () => { try { server.post("/oracle", async (req, res, next) => { const request = JSON.parse(req.body.request); const sql = request.sql; const binds = request.binds; const options = request.options; const result = await execOracle(sql, binds, options); res.json(result); return next(); }); server.post("/mysql", async (req, res, next) => { const request = JSON.parse(req.body.request); const result = await execMysql8(request); res.json(result); return next(); }); process.once("SIGTERM", closePoolAndExit).once("SIGINT", closePoolAndExit); await init(); server.listen(process.env.PORT || 3000, function() { console.log("Server started."); }); } catch (err) { console.error(err); } })(); Smalltalkライブラリ Pharoに以下のライブラリをロードします SeasideとNeoJSONのインストール Metacello new baseline:'Seaside3'; repository: 'github://SeasideSt/Seaside:master/repository'; load. Gofer it smalltalkhubUser: 'SvenVanCaekenberghe' project: 'Neo'; configurationOf: 'NeoJSON'; loadStable. Pharo テストで使用したコードは以下の通りです。 実際に使う際にはラッパークラスを作って運用することになると思います。 apiサーバーからはJSONでデータが帰ってくるので、NeoJSONReaderでSmalltalkオブジェクトに変換することで簡易的にORマッパーも兼ねられました。 Oracle接続テスト NeoJSONReader fromString: ( ZnClient new url: 'http://localhost:3000/oracle'; formAt: 'request' put: (NeoJSONWriter toString:( Dictionary new at: 'sql' put: 'SELECT * FROM hoge WHERE id=:1'; at: 'options' put: (Dictionary new at:'outFormat' put: 4002; yourself); at: 'binds' put: (Array with: 1); yourself )); post ). MySQLでもコネクションプールを使うので接続先のデータベースは都度選択する想定です。 MySQL接続テスト NeoJSONReader fromString: ( ZnClient new url: 'http://localhost:3000/mysql'; formAt: 'request' put: (NeoJSONWriter toString:( Dictionary new at: 'sql' put: 'use fuga; SELECT * FROM hoge WHERE id=?'; at: 'timeout' put: 40000; at: 'values' put: (Array with: 1); yourself )); post ). まとめ これで、WindowsPCで開発してLinuxサーバーで運用するスタイルが確立出来ました。 余談 PharoのLibCクラスのresultOfCommand:を使えばOSProcess同様にWindowsのコマンドが実行できるのがわかったので、最初はnode.jsのプログラムをPharoからLibCで呼び出すようにしましたが、Oracleデータの取得に1300ミリ秒（内データベース接続に900ミリ秒）もかかってしまうため実用になりませんでした。 プログラムを毎回起動するとデータベース接続も毎回行われるため、コネクションプールを使う＝サーバー化することで、データの取得が20ミリ秒に短縮出来ました。

この記事について この記事は、2021年4月10日にどいこさんが主催した【オンラインハンズオン】はじめてのSQLのレポート記事です。 SQL勉強会で扱った内容を、どいこさんがQiitaにまとめてくださっております！ どいこさんのQiitaはとても説明が分かりやすく、また、「間違ったSQL文を正しく直す」「問題文を読みながらその処理を行うSQLを自分で考えてみる」といった練習問題もありますので、楽しみながらSQLの知識を定着させる事のできるものになっております！ https://qiita.com/doiko/items/0b4c5353dccd8497b0a8 そもそもSQLとは データの操作や定義を行うためのデータベース言語 wikipediaより引用 データベースに接続、プログラムを通じてデータベース内のデータを操作する時に、Java、Ruby、PHP、etc...といった他のプログラミング言語と併用される。 データベース言語とは コンピュータのデータベースを扱う為の言語 wikipediaより引用 データベース言語には、いくつか種類があって データベース内にある情報を、取得・追加・編集・削除する為の、データ操作言語 新しくデータベースやテーブルを作成する、または今あるデータベースやテーブルを削除・編集する為のデータ定義言語 データベースにアクセスできる人を制御する為のデータ制御言語 この三つの種類のデータベース言語がある こちらのサイトを参考にしました データベースとは ある特定の条件に当てはまるデータを複数集めて、後で使いやすい形に整理した情報の塊の事 こちらのサイトから引用しました ざっくりと説明すると、データを保管して管理する為のExcelのような物 [データベースを扱う時に使われる用語] テーブル・・・データを格納する表のような物(Excelでいうシートのようなもの) カラム・・・テーブルの列(項目にあたるもの) レコード・・・テーブルの行(1件分のデータにあたるもの) フィールド・・・レコードを構成する一つ一つの要素の事(Excelでいうセルに当たる部分) SQLを書いてみよう！ 今回の勉強会では、こちらのWEBブラウザ上でSQLを実行出来るツールを使用しました また、SQLはPostgresqlを使用しました SQLの基本ルール SQLにはいくつかのルールがあって 改行・スペース SQL文の途中で、改行する事が出来る 行の先頭や、途中に半角スペースを入れる事が出来る SELECT文 SELECT * FROM menus WHERE id = 2; #改行無しのSELECT文 SELECT * FROM menus WHERE id = 2; #改行ありのSELECT文 #SQL文が長くなった時に、見やすく書く事が出来る コメント そのSQL文についてのコメントを残す事が出来る ハイフン(-)を二つ続けて書くと、ハイフンの後ろから行の最後までをコメントとする事が出来る /* から */までをコメントとする事が出来る。複数行のコメントを書く時に使う SQL文 -- ハイフンを使うと、2つから行の最後までがコメントとなる /* /*から、*/までがコメントととなる。この書き方を使うと複数行のコメントが書ける */ 予約語 予約語とは ざっくりと説明するとプログラミング言語や、データベース言語において既に役割の決められている文字列の事 SQLでいう、SELECT・UPDATE・DELETEなどの命令に使う文字列が予約語にあたり、テーブル名やカラム名に使う事が出来ない (どれが予約語で、どれがテーブル名・カラム名か分からなくなる為) 予約語を書く際は、大文字でも小文字でも動作する SELECT文 SELECT * FROM menus WHERE id = 2; # SELECT、WHEREといった予約語を大文字で書いた場合 select * from menus where id = 2; # 小文字で書いた場合。小文字で書いても動作する テーブルの作成 (CREATE TABLE文) CREATE TABLE文は、テーブルの名前や、どんなデータを入れるカラムなのかを定義する為のデータ定義言語 データベース内に、データを格納する為のテーブルを作成する為のSQL文 create文 CREATE TABLE menus ("id" int, "category" varchar(8), "name" varchar(20), "price" int) ; #CREATE文の書き方 #CREATE TABLE テーブル名( カラム名 データ型 ) #カラムが複数ある場合は、カンマ(,)で区切る Postgresqlでは、テーブル名やフィールド名を「"」（ダブルクォート）で括ることにより、特殊文字をテーブル名やフィールド名に含めることが出来ます。 また、ダブルクォートを用いて作成したテーブル名は SELECT 等で使用する際にもダブルクォートで括って指定する必要があります。 https://koumei2.com/wiki/1183#:~:text=PostgreSQL%20%E3%81%A7%E3%81%AF%E3%80%81CREATE%20TABLE%20%E6%96%87,%E6%8C%87%E5%AE%9A%E3%81%99%E3%82%8B%E5%BF%85%E8%A6%81%E3%81%8C%E3%81%82%E3%82%8A%E3%81%BE%E3%81%99 より テーブルにデータを追加する (INSERT文) insert文 INSERT INTO menus ("id", "category", "name", "price") VALUES (1, '定食', '焼きそば定食', 680), (2, '定食', 'ハンバーグ定食', 680), (3, '定食', 'ミックスフライ定食', 800), (4, '単品', 'カレーライス', 600), (5, '単品', 'オムライス', 800), (6, '単品', 'シーザーサラダ', 580), (7, 'デザート', 'ケーキ', 480), (8, 'デザート', 'プリンパフェ', 600), (9, 'ドリンク', '生ビール', 380), (10, 'ドリンク', '焼酎', 500), (11, 'ドリンク', 'コーヒー', 300) ; #insert文の書き方 #INSERT INTO テーブル名 (カラム名) VALUES (値) ; #カラム名、値が複数ある場合はカンマ(,)で区切る #文字列はクォーテーションで囲う事で文字列に、数値は何も囲わない事で数値として扱われる INSERT文を使う時、PostgreSQLなどの標準SQL(MySQL以外)では シングルクォーテーションで囲うと文字列 ダブルクォーテーションで囲うとカラム名 MySQLでは シングル・ダブルクォーテーションで囲うと文字列 バッククォートで囲うとカラム名 として、扱われます こちらのqiitaを参考にしました データを検索・取得する (SELECT文) SELECT文 # SELECT文の書き方 SELECT カラム名 FROM テーブル名 # 複数のカラムを指定する事も出来る。その際はカラム名をカンマで区切る SELECT カラム名1, カラム名2 FROM テーブル名 # カラム名の所を*とすると、全てのカラムの値が取得出来る SELECT * FROM テーブル名 カラムに別名をつける (AS句) AS句というのを使うと、カラム名・テーブル名に別名(エイリアス)をつける事が出来る AS句 SELECT name AS メニュー名, price AS 価格 FROM menus AS メニュー # ASの使い方 SELECT カラム名 AS なんて名前にしたいか FROM テーブル名 AS なんて名前にしたいか # ASは省略する事も出来る SELECT カラム名 なんて名前にしたいか FROM テーブル名 なんて名前にしたいか どんな時に別名をつけるか 複数のテーブルがデータベースの中にある場合に、どのテーブルのカラムなのかを分かりやすくしたい時(SQLの見やすさを意識したい時) こちらのサイトを参考にしました テーブル内のデータを編集する (UPDATE文) UPDATE文 # UPDATE文の書き方 UPDATE テーブル名 SET カラム名 = どの値に更新するか WHERE どの行を更新するかの条件 (条件を指定しないと全ての行の指定したカラムの値が編集される) # 例文 UPDATE menus SET price = 100 WHERE name = '生ビール' # どの値に編集するのかは複数指定する事が出来る。その際はカンマ(,)で区切る UPDATE menus SET price = 100, name = '生中' WHERE name = '生ビール' テーブル内のデータを削除する (DELETE文) DELETE文 # DELETE文の書き方 DELETE FROM テーブル名 WHERE どこの行を削除するかの条件 (条件を指定しないと全ての行が削除される) DELETE文は特定のカラムではなく、特定の行を削除するSQL文なのでカラムの指定をする必要は無し またここまでに出てきたSQL文は、システムに必要な データの作成(CREATE) 読み取り(READ) 更新(UPDATE) 削除(DELETE) の処理を行うSQL文。それぞれのSQL文の頭文字を取り、CRUD（クラッド)と呼ぶ 条件の設定 WHERE句 WHERE句を使う事で、取得するデータの条件の設定をし、データを絞り込む事が出来る INSERT文以外のSQL文(SELECT、UPDATE、DELETE)で使用する事が出来る またWHERE句を使って条件を絞り込む時は、必ず正しい条件を書いて絞り込む事 どんな条件を書いたらいいの？ 結果が必ずTRUEかFALSE(真か偽)になる条件式を書く SELECT文 #例 SELECT FROM * menus WHERE name = '唐揚げ' # 名前カラムの値が唐揚げと同じ物 SELECT * FROM menus WHERE price > 600 # 値段カラムの値が600円よりも大きい物 記号(演算子)の種類と意味 記号(演算子) 意味 = 左右の値が等しい < 左辺は右辺より小さい > 左辺は右辺より大きい <= 左辺は右辺の値以下 >= 左辺は右辺の値以上 <> 左右の値は等しくない NULLとは そこに何も格納されていない、未定義の値 どんな時に使うのか たとえばユーザー登録時の住所の情報を格納するテーブル「addresses」があったとして 住所の登録には必ず、郵便番号や住んでいる都道府県の入力が必要になる為、「郵便番号」や「都道府県」の列は必ず値が存在するはずですが、 一戸建てに住んでいる方の場合だとマンションに住んでいるわけではないので、「マンション名・部屋番号」の列には一戸建てのユーザーの場合格納できるデータがないかと思います。 そんな場合にNULLとしたり、空文字（' '）を格納したりします。 また、このあたりはどういう仕様にするのかによって変わって来るそうです カラム内の値がNULLかどうかの判別の仕方 WHERE句を使うのは、他のと変わらないけれども SELECT文 SELECT * FROM menus WHERE name = NULL #　この書き方では、判別出来ない SELECT * FROM menus WHERE name IS NULL # =ではなく、IS NULLを使う事で判別が出来る SELECT * FROM menus WHERE name IS NOT NULL # NULLではない値を取得する時はIS NOT NULLを使う なぜ、カラム名 = NULLでは判別できないのか ＝や>といった演算子は、値同士を判別する為のものであり、NULLは値ではない為 指定した範囲のデータを取得する (BETWEEN演算子) 例えば値段が〇〇以上〇〇以下のものといったように、値の上限値と下限値を指定する事で、値がその範囲内に収まっているデータを取得することができる SELECT文 # BETWEENの書き方 SELECT * FROM menus WHERE カラム名 BETWEEN 下限値　AND 上限値 複数の値の指定 IN演算子 カラムの値が指定した値のリストの中に一致するものがあるかどうか調べる事が出来る ＝だと1つの値との比較しかできないけど、INならたくさんの値との比較が出来る SELECT文 # INの書き方 SELECT * FROM menus WHERE カラム名 IN(値1,値2) # ()カッコ内の値は、カンマ(,)で区切る SELECT * FROM menus WHERE category IN('定食','単品') # カテゴリーが定食、単品の行を取得するという意味 # NOT INを使うと、リスト内の値以外のものがあるかどうかを調べることが出来る SELECT * FROM menus WHERE category NOT IN('定食','単品') # カテゴリーが定食、単品以外の行を取得するという意味 カラムの中の値がNULLの場合はNULLを返す パターンマッチングを行う (LIKE演算子) 今までに出てきた演算子は、特定の単語と完全に一致しているかをチェックしていたが、LIKE演算子を使うと完全一致ではなく「特定の単語を含んでいるか」というチェックの仕方が出来る また、LIKE演算子には%と_という記号も一緒に使う 記号 意味 % どれでもいい0文字以上の文字列 _ どれでもいい一文字 SELECT文 SELECT * FROM menus WHERE name LIKE '単語と記号' # 例えば SELECT * FROM menus WHERE name LIKE '%定食' # %は0文字以上の文字列として扱う事が出来るので、〇〇定食という名前のものはあるかをチェックしている SELECT * FROM menus WHERE name LIKE '焼き%' # この場合は、焼きの後ろに0文字以上の文字列はあるかという意味なので、焼き〇〇という名前のものはあるかをチェックしている 論理演算子　(AND、OR、NOT) AND AかつB　（2つの条件式両方に一致する場合のみTRUEとなる） SELECT文 条件式A AND 条件式B UPDATE文 UPDATE menus SET price = 600 WHERE category = 'ドリンク' AND price > 300 # カテゴリーがドリンクかつ値段が300円以上の物の価格を600円にするという意味 OR AまたはB　（2つの条件式のどちらかに一致するならTRUEとなる） SELECT文 条件式A AND 条件式B SELECT文 SELECT name, price FROM menus WHERE name = '生ビール' OR name = '焼酎 # 名前が生ビールまたは、焼酎の物の値段を取得するという意味 NOT 条件式に当てはまらない場合にTRUEとする SELECT文 SELECT name, price FROM menus WHERE NOT name LIKE ('%サラダ%') #名前がサラダ以外の物の名前と価格を取得するという意味 論理演算子の注意点 複数の論理演算子を使ったSELECT文を実行する時は、前から順番にではなく 優先順位の高い演算子から実行される 例えば、定食または単品で、nameにライスまたはフライとつくメニューを抽出する場合 SELECT文 SELECT category, name, price FROM menus WHERE category = '定食' or category = '単品' AND name LIKE ('%ライス%') OR name LIKE ('%フライ%') このSELECT文だと、うまく抽出が出来ない なぜなら、 論理演算子の実行順番は 1.NOT 2.AND 3.OR となるので、まずANDが実行されて「単品かつ名前にライスを含むもの」が抽出された後 「定食」OR 「単品かつ名前にライスを含むもの」OR 「名前にフライを含むもの」の結果が返る為 どうすれば、狙い通りの抽出出来るか SELECT文 SELECT category, name, price FROM menus WHERE ( category = '定食' or category = '単品' ) AND ( name LIKE ('%ライス%') OR name LIKE ('%フライ%')) 上記のようにカッコ()を使い、優先順位をあげると狙い通り抽出出来る 以上になります！ご拝読頂き有り難うございました!