MySQL SQL質問応答スレ [無断転載禁止]©2ch.net
RDBMSごとにスレが分かれていないので答えようがない質問ばかりなので独立させました。
SQL質疑応答スレ 17問目 [無断転載禁止]©2ch.net
http://echo.2ch.net/test/read.cgi/db/1468157341/ 誰でも簡単にパソコン1台で稼げる方法など
参考までに、
⇒ 『宮本のゴウリエセレレ』 というブログで見ることができるらしいです。
グーグル検索⇒『宮本のゴウリエセレレ』
XS7TY79BP2 データベースが壊れて、mysqldが起動できません。mysqldが動いていなくてもmysqldumpは動くのでしょうか? /var/log/mysql.logを見ていたら、
2月中の一週間分綺麗にログが消えていました。
その期間サイトが使えなかったことはありませんでした。
これって何が考えられますか? opened_tablesとtable_open_cacheについて教えてください。
この調整をしているのですが、
opened_tables < table_open_cache
二週間ほど様子見てきて、
opened_tablesの数値が一定化してきました。
それに合わせてtable_open_cacheを小さくすると、
それに伴ってopened_tablesが大きくなったりしてしまうんでしょうか? nullを許可したnullのカラムに、nullで更新をかけると、更新タイムスタンプが変更されてしまいますが、仕様ですか?
同カラムの値が1で、そのまま1で更新をかけた場合はタイムスタンプは更新されませんでした。 初歩的な質問ですが、
ダンプでMySQLデータをエクスポートするのと、
phpMyAdminでテーブルを選択してエクスポート。
どんな違いがありますか? wordpressをサーバー移転するため、新サーバー(Xサーバー)にて新規データベースを作成、
ユーザーを割り当て、phpmyadminを開いたのですが、「インポート」のタブを押しても何も変わりません。
それどころか、どのタブを押しても、表示が変わりません。「一般設定」と「外観設定」についてが表示されたままです。
もうどうしたらいいかわかりません…。 レプリケーションって1度構築したら
マスターやスレーブの再起動があったり
ip振り直しがあったあと再度有効になりますか?
例えば仮想マシンイメージにビルドしておいて
mysqldサービス自動起動に設定しておいて
ネットワークだけ繋げて
それぞれの仮想マシン起動したらまたレプリケーション
されるようになる?
その都度内部にログインして作業が必要? create文を調べていたところ下記2通りの記述を見つけました。
create table xxxx default charset = utf8
create table xxxx default character set = utf8
ともにテーブルの作成はできたのですが、作成後のテーブルの違いはありますでしょうか?
もしある場合何が違うのか教えてください。 >>11
auto.conf を消さないと uuid の衝突でレプリが止まるから駄目
あまりそういうふうな運用はしないほうがいいかも >>9
mysqldump にもオプションが色々あるから一概に一緒とは言えない データベースに設定するユーザ名って
WindowsやLinuxのユーザ名のこと?
それともデータベース独自のユーザー? >>16 自己解決。それぞれのユーザー名は無関係でした。 Linuxユーザーです。
localhostでデータベースの学習用にMySQLを使います。
https://dev.mysql.com/doc/refman/5.6/ja/changing-mysql-user.html
によると、その場合、
通常のユーザーアカウントでmysqldサーバーを実行したほうが
セキュリティ上よいと書かれてあります。
皆さんはそうなさっていますか?
逆にセキュリティ上の脅威になりそうな気もするんですが。 お願いします。
実行すると、なかなかプロンプトが戻ってこないSQLがあります。
こういうSQLを実行してる時って、
@サーバ自体にものすごい負荷(ディスクIOやCPU資源)がかかってたりするのでしょうか。
Aそれとも単にSQLが遅い(=SQLを利用するアプリケーションの動作が遅くなる)だけなのでしょうか。
よろしくお願いいたします。 最近の質疑応答スレって特定の処理系に依存するものが多いね
次スレ立てず、処理系毎に分けてしまう方が良いかも phpの中でsql文を変数に格納したいのですが。
一応、次の構文で通ります。これはsqlインジェクション的に問題ありますでしょうか?
$query = "SELECT * FROM emp_table WHERE job = '".$job."' ORDER BY emp_id"; >21
残念ですが、それでは何の解決にもなってない
もう一度SQLインジェクションについて勉強汁
なおSQL構文を変数に入れて使うこと自体は何の問題もない テーブル T
コード 履歴番号 本文
A 1 aaa
A 2 bbb
A 3 ccc
B 1 ddd
B 2 eee
C 1 fff
C 2 ggg
抽出後
コード 履歴番号 本文
A 3 ccc
B 2 eee
C 2 ggg
コードでgroup byして履歴番号が最大のものだけを
抽出したいですが
本文は失いたくありません
なにか上手い方法はありますか? ちょっと雑で済まん
select コード,履歴番号,本文 from T,
(select コード as c, max(履歴番号) as r from T group by コード) w
where コード=c and 履歴番号=r; 集約関数が使えるのはSELECT句とHAVING句だけだと思っていたのですが、
ORDER BY COUNT(*);
と書いてもエラーになりません。
集約関数が書ける句を全て上げると、どの句がありますか? 下記の@が、Aの様に書けないのはどうしてでしょうか?
ご存じの方、いらっしゃいましたら教えて頂けると嬉しいです。
宜しくお願い致します。
@
SELECT shohin_id,
shohin_mei,
shohin_bunrui,
hanbai_tanka,
(SELECT AVG(hanbai_tanka) FROM Shohin) AS hanbai_tanka_all
FROM Shohin;
A
SELECT shohin_id,
shohin_mei,
shohin_bunrui,
hanbai_tanka,
AVG(hanbai_tanka) FROM Shohin
FROM Shohin; >>29
GROUP BYとグループ関数の使い方を正しく学習してくるんだ
グループ関数はGROUP BYが必須(全行が集計対象になる場合のみ省略可) >>30
なるほど、それで@の内側のAVG()関数はSELECT句に書いてもエラーにならないんですね。
解りやすいです。ありがとうござました! 伝票id 社員id 名前 数
1 a 山田 10
2 b 田中 20
select 社員id,名前,sum(数)
from table
group by 社員id with rollup
結果
a 山田 10
b 田中 20
null 田中 30
一番下の田中を、小計を出さずにnullにする事はできますか? 自分でwith rollupを指定して何を言ってるんだ? >>32
突っ込まないから、どういう結果にしたいかを書いてみて 解決しました。
select *
from (
select 列1,列2,sum(合計列)
from テーブル名
group by 列1,列2
with rollup
) as テーブル
where (列1 is not null and 列2 is not null)
or (列1 is null and 列2 is null)
サブクエリを使わずhavingを使った例もあったけど自分の環境では合計行が出なかった。 改定前の「ゼロからはじめるデータベース操作 SQL」という本の177ページ問5.3に載っている問題なのですが、
下記の様に商品分類ごとに平均販売単価を求めるSQL文で、
「GROPU BY S1.shohin_bunrui」のS1.がどうして必要なのか分りません。
これを付けなくても結果は同じになります。
これがどうして必要なのか分る方いらっしゃいましたら教えて頂けると助かります。
CREATE VIEW AvgTankaByBunrui AS
SELECT shohin_id,
shohin_mei,
shohin_bunrui,
hanbai_tanka,
(SELECT AVG(hanbai_tanka)
FROM Shohin S2
WHERE S1.shohin_bunrui = S2.shohin_bunrui
GROPU BY S1.shohin_bunrui) AS avg_hanbai_tanka
FROM Shohin S1; 憶測なんですがねぇ…
省略した場合はS2のshohin_bunruiが参照されるはず
でもS1.shohin_bunrui = S2.shohin_bunruiが有るので
S1でもS2でも結果は同じだから許されるのかもしれない あ、MYSQLだからS1への参照許されるはず
他RDBMSだと多分エラーになる。S2ならば参照可能 ていうかその本はまれに見るクソだな…
AVG(hanbai_tanka)はあくまでshohin_bunruiに関連するデータなので
shohin_idやshohin_meiの列とは最早何の関連も無い情報なのよ
それを無理やり結合して取得するやり方は意味不明バグの原因にしかならんよ >>40
ちょっと何言ってるかわかりません
GROPU BYがおかしいのを除けば、普通の相関サブクエリだと思うが
まあいまどきはあんまり使わんがな
つかあいかわらずMySQLはでたらめなSQL通すなぁ
初心者の勉強にはむいてないと思うわ >>37
まず翔泳社のサイト行って正誤表確認してこい
https://www.shoeisha.co.jp/book/detail/9784798118819
5.4の解答、ビューの作成文のGROUP句とかスゲー気になるわw
つかこれだけ修正でてるのに電子書籍版が一切修正されてないのかよ
電子書籍版買っちゃだめだな、これ >>42
元質問にそう書いてあるからそう書いてるんだが、と言っておこう
まあ、気づいてなくてコピペしたからなんだがw
脳内補正おそるべし MySQLにPHPmyadminからodsファイルを読み込ませたら、PHPで出力する時に
「水○○」って入力してある文字が、水の部分だけ消えてしまうんですが
水って駄目文字とかなんかあるんでしょうか? ここで良いのか分かりませんが質問です
検索文字列が複数列のどれかに一致するデータを取得したいのですが
WHEREの中は
aname1 = '探し物' OR aname2 = '探し物' OR aname3 = '探し物' OR …とやるしかないのでしょうか
80個くらいあるのでもっと簡単にできる方法がありましたら教えてください 例えばそれ以外のカラムにはなく、aname1 には '探し物' があるような
データに特別な意味を持つテーブルなのかどうか >>48
ちょっと考えてみます
>>49
テーブルの方の例ですが
aname1:りんご
aname2:リンゴ
aname3:林檎
と入れてありまして、どの文字列でもこのレコードの情報(価格や産地など)を得たい
ということなのです 適当な区切文字を選んで、(例えば$$として)
aname に 'リンゴ$$りんご$$林檎' をセットする
検索時にaname like '%keyword%'ってやる >>51
うーむそういうものですか…DB扱うの初めてなもので…
>>52
なーるほど
と思いましたが他の例えば「りんご飴」レコードも検索されてしまいますので
でも文字数チェックすればいけるかも
でもそもそもそういう風に使用すべきで無いとの意見もありましたし
もう少し考えてみたいと思いますありがとうございました 'りんご飴'にヒットさせたくないなら
aname like '%$$keyword$$%' >>54
う、それいけるかも
元々csvからインポートしてるだけだけど工夫したら自動で変換もできるかも
色々ご相談にのって頂いてご意見いただきましてありがとうです >>50
それで1つのレコードを指し示す名前が80個にもなるの?
リンゴの例だと想像つかないけど >>56
了解です
結局 or 羅列方式で進行中ですが…
>>57
アドバイスありがとうございます今後検討してみたいと思います
>>59
りんごの例ですとせいぜい数個なのですが
食品名って地方によって呼び名が変わったり
別名があったり
それぞれひらがなカタカナ漢字の組み合わせがあったりで
余裕を持って80にしてみたのです
登録中ですけど今のところ最大で20個くらいですが… >>60
なるほど、じゃ正式名称として使うものは元のテーブルにも残しつつ
>>57が書いてるような別名用のテーブルを用意するのがよさそう >>61
別に専用テーブルを設けるのが一番美しいと言うか普通なのかな
ひとつで管理の方が簡単かなとも思ったんですけどRDBってくらいだし面白そうだし
ちょっと本気で検討してみます
つか別の案件が急に入ってしまって(何とdelphi案件!)少し寝かしておいてまた質問等ありましたらよろしくお願いします。
delphiはくだすれがあったような遠い記憶… 「名前」「メールアドレス」など、多数の文字列が入らないカラムの場合、
データ型はtextで良いですかね?それともvarcharで指定するべきですか? そのフィールドを頻繁に検索するならvarcharの方がいいんじゃね?
稀に使う程度ならどっちでもいいけど RFCの規定だと、全体の最大長は256文字だそうだ >>65
like検索はしますが、=検索はしないという想定です。
指定したほうが検索が早いのはわかるのですが、
それ以外にメリットありますかね?
(文字数制限はアプリ側でするとして >>67
5.7系の場合はtemp tableが必要になった場合の速度が違う
8.0系の場合はTempTableエンジン使っていれば性能的な差は気にしなくてもいいけど
Postgresと違ってMySQLのtext型は掲示板のレスのような長い可変文字列を格納する型として定着してるので
メールアドレスや名前みたいなのに使うと他の人が見ると混乱する
DBのデータ型でも簡単に文字数制限できるのに
それをアプリだけでやるというのは良くない設計 実際に格納されている文字列長分の領域しか使っていない、事前に領域を確保していない、使わなくなった領域は穴埋めで再利用されると思うのは、データベースエンジニアでもいるくらいだからなあ。
データの物理位置を常に移動させていたら、とてもじゃないが、使い物にならない速度になってしまう。
なんとなく大きなデータ型を使うのは、アンチパターンどころか、絶対にやってはいけないレベル。 スキーマ名にutf8な日本語が含まれている場合に、mysqldumpでうまくスキーマごとにダンプすることってできますか?エロい人。
Windows版の5.7です。 ローカル環境の8でTEXT型20万行に対しnot in検索かけたらこの世の終わりみたいな音出してたよ マルチバイトを使わない文字列が1カラムあるテーブルがあって
password
Password
PASSWORD
あって、区別するにはBINARYを使うというまではわかったんだけど、
このカラムにプライマリキーやuniqueを設定しようとすると重複してダメと言われるけど、大文字小文字が混在する場合は無理?
1カラムしか無い場合はプライマリーキーとかは設定しなくていいの? プライマリキーを設定するかどうかはcollationとは別の問題 レコード追加する際に、連番のカラムを最大値+1にしたいです。
諸事情で、
・その連番のカラムはAUTO_INCREMENTではない
・プライマリキーでもなければUNIQUEですらない
・テーブル定義は変更してはいけない
という状況で、
重複させないのが要件です。
INSERT INTO unko (renban) SELECT MAX(renban)+1 FROM unko;
として1つのSQLで実行すれば、重複のリスクは回避できるでしょうか?
それとも、トランザクションでテーブル排他ロックからしなきゃだめでしょうか? SQLについて質問なのですが
select USERID,USERNAME from USER_GRADE
where
STATUS in (select STATUS from USER_GRADE where GRADE ='Platinum' group by STATUS)
and
GRADE <> 'Platinum'
これをSQL初心者にもわかる簡単な書式に直して同じ結果を出したいのですがわかる方いますでしょうか >>76
トランザクションによる排他制御は必須
それが暗黙的に実行されるか明示的に指定するかの違いがあるだけ
そのケースは分離レベルをserializableにする必要がある
同じSQLステートメントでしか競合しないなら
片方のトランザクションをコミット直前でsleepさせておいて
もう片方のトランザクションをsleepさせずに実行した結果を確認してみるといい すみません、ちょっと筋違いな質問かもしれませんが、
あるソフトウェアはSQLを利用するのですが、それをインストールしようとすると、他のソフトウェアで使われているSQLと競合してインストールできません。
他のソフトウェアはたくさんあるのですが、どれがSQLを使用しているものか調べる方法はありますでしょうか? mysqltunerで
skip-name-resolve=1
これの推奨が出てくるのは、いつもlocalhostと127.0.0.1の変換がパフォーマンスの低下になるからってこと?
ユーザ作る時localhostと手打ちで入力しているけど、
ローカルだけならホストの権限は127.0.0.1に揃えた方が良いと言うこと? MYSQLで
CREATE OR REPLACE TABLEみたいなことってできませんか?
テーブルの作成、もしくはテーブル構成の更新したいのです 検索方法で質問です
例えば「カツカレー」で検索すると
「カレー」のレコードを
「三色エビチリ」で検索すると
「エビチリ」のレコードを得たいのですがいい方法はありますでしょうか
テーブルの方は
カレー 情報…
エビチリ 情報…
オムライス 情報…
となってます >>86
それマスタテーブルの存在がわかってないだけだろ >>86
形態素解析の話?
それとも「カツカレー」という単語を「カレー」にマッピングするテーブルを作る話? >>88
集合演算子で検索したけど
分からなかった
>>89
あ、いえ例えばカレーで検索してカツカレーを求めることはできますけど
正規表現とか使って逆はできないかな
という質問です
マッピングテーブルみたいなの作るしかないのかな >>90
カツカレーの情報が、エビチリ情報テーブルに存在しないデータの持ち方ならSELECT文をUNION ALLで並べればいいだけだろ。
セレクト 列名 カレー情報 where 列名 イコール カツカレー
ユニオンALL
セレクト 列名 エビチリ情報 where 列名
イコール カツカレー
俺は答えを教えるのは本人にとってよくないと思っているから、本当は書きたない。
そもそもそのデータモデリングは自分で考えたのか? UNIONなどの集合演算子は列名が揃ってないと使えないからな >>91
おまえどう考えても勘違いしてるぞ
上塗り重ねず自重しとけ >>90
選択肢はブルートフォース、n-gram、 形態素解析、マッピングテーブルくらいかな
どれを選ぶかは用途次第
ブルートフォースは「カツカレー」というキーワードとテーブルの列名を1件1件比較して
キーワード内の部分文字列が列名に合致するかどうかを判断していく
めちゃ遅くなるけどデータ量と要求速度によってはこれでいい場合もある
n-gramで例えば文字単位で3文字ずつのまとまり(trigram)に分解すれば
「カツカレー」は「カツカ」「ツカレ」「カレー」の3つになる
この3つのキーワードともとの単語でテーブルを検索して合致するものがあるかどうか見る
形態素解析はそれ用のライブラリ使って
「カツカレー」を「カツ」と「カレー」という単語単位に分解して検索する
マッピングテーブルは
「カツカレー」というキーをマスタテーブルの「カレー」にマッピング
「カツ」にマッピングしたりする可能性もあるから1対多 動的に列名違いのSQLを組み立てるなんてありえない。 >>95 >>96
SQLで出来ないことが分かっただけでも収穫ですが
さらに解決のヒントをいただきありがとうございます
ブルートフォースって言うのですか
PHPで作ってますがロジックで判断するやり方はちらっと頭に浮かんだのですが
性能がめちゃ心配で脳内却下してました
n-gramはちょっと分かりませんでした
形態素は解析エンジンを信用しきれなくて…
というわけで力作業でマッピングテーブルを作るか
それかブルートフォースでやるか
検討したいと思います >>97
SQLでというか>>95の選択肢はどれもストアドやUDF使えばDB側でできるよ
特にブルートフォースでやるならDB側でやったほうが性能的には断然いい