読者です 読者をやめる 読者になる 読者になる

mysql group_concatにorder byを指定する時の注意事項

group_concatにorder byを指定しても意図したソートにならないケースがある。 まず以下のように、group_concatに指定したcategory_idをそのカラム順でソートするケースの場合は問題ない。 group_concat(category_id order by category_id)問題となるのは以下…

mysql テーブル設計 カンマ区切りのカラムについて考察

例えば商品の属性とかレコードに紐づくデータが沢山あってレコード毎にデータ作ると色々面倒なケース。DB設計の基本について 例えば一つのレコードが複数のカテゴリに… - 人力検索はてな MySQLでカンマ区切りの値を登録してみる | シスデイズ技術ブログ まさ…

mysql group_concatとfind_in_setのまとめ

group_concatはgroup byで複数行をまとめた際に、まとめられた列のデータをカンマ区切りで表示する機能。find_in_setはカンマ区切りのデータを絞り込む機能。この2つを組み合わせることで、本来なら副問合せ等の複数のSQLに分けないと出来ないようなことが一…

sql joinの条件とwhereの条件の違いについて

ケース1 left join t2 on t2.t1_id = t1.id AND t2.status = 'ERROR' ケース2 left join t2 on t2.t1_id = t1.id 中略 where t2.status = 'ERROR' ケース1の場合は結合前の条件となるため、ERRORでないt2のデータもNULLとしてデータを取得する。 ケース2の場…

mysql5.6が突然起動しなくなった件の原因と対策

昨日まで動いてたmysqlが突然起動できなくなった。以下、ログ周りのオプションを変更してみた時のエラーログまとめ # error1 /usr/sbin/mysqld: File './mysql-bin.000023' not found (Errcode: 28 - No space left on device) 2016-09-14 08:52:37 23762 [E…

awsでmysqlが突然起動しなくなった件

awsでmysqlが突然落ちて再度起動しようとしたときのエラー 160731 16:54:25 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 2016-07-31 16:54:25 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --…

mysql auto_incrementをダンプから外したい場合

skip-optオプションを使うとエンジンとかも表示されなくなるため、以下のようにsedでやるしかないらしい。 mysqldump -u user db_name -p -d | sed 's/ AUTO_INCREMENT=[0-9]*\b//' > db_name.sql

mysql 外部サーバーからアクセス可能なユーザーを作成する

別サーバー(host2)から、例えばlocalhostからしかアクセスできないユーザー(user1)でDBサーバー(host1)にログインしてみる。 $ mysql -uuser1 -ppass -h host1 ERROR 1045 (28000): Access denied for user 'user1'@'host2' (using password: YES)このように…

変更される可能性のあるカラムを他のテーブルに持たないこと

基本中の基本の事が頭から漏れていたので漏れないように書く。userテーブル id user_nameuser_infoテーブル id user_name oldこれだとuser_nameが変更される度にuser_infoのuser_nameまで更新しないといけない。 例え、既にそのような設計になっていたとして…

phpmyadminでデータコピー

今更だけどphpmyadminで行単位でデータコピーできないのかと思って調べたところ、できたのでメモ。表示>(コピーしたい行の)編集>値を編集>新しい行として挿入する>実行

mysqlで自然順でソートさせる方法

mysqlはphpのnatsortのように自然順で検索させる機能がないっぽいので、むりやり自然順でソートさせたい場合は、lpadを使って桁数を合わせればよい。 mysql> select id,lpad(id,10,'0') from users order by lpad(id,10,'0') desc; +----------+------------…

mysqlのデータをtsv出力するコマンド

何度も忘れてその度に調べるのでメモ mysql -u user -ppass -h host db_name -e "select * from table" > table.tsv

mysqlのToo many connectionsエラーについて

mysqlはデフォルトで最大接続数が100に設定されている。 それを越えると接続エラーとなる。(以下はcakephpが出したエラー) 2014-07-21 11:06:09 Warning: Warning (2): mysql_connect() [<a href='http://php.net/function.mysql-connect'>function.mysql-connect</a>]: Too many connections in [CORE/cake/libs/…

mysql テーブルロック&行ロックまとめ

よく忘れるのでメモInnoDBで行ロック/テーブルロックになる条件 - (゚∀゚)o彡 sasata299's blog MyISAM・・テーブルロック InnoDB・・行ロック InnoDBでも制約のないカラムに対して更新をかけるとテーブルロックになるMySQL InnoDBの行レベルロック 共有ロック…

mysqlでfloatを使う際の注意点

消費税ネタ mysqlでfloat型のカラムに1.05という値が入っていたんだけどselectしてもヒットしない。 select * from table where col = 1.05; -- Empty set (0.00 sec) どうやらfloat型の場合、単純に比較が出来ないらしい。で、とりあえず1.05のデータを抽出…

数字を扱うカラムはint型にすること

時間をテーブルに保存する際に、2桁以内だからという理由でvarchar(2)にしていたけど、これだとORDER BYした時に、希望通りにソートされない。例えば、22時と9時。varcharのカラムに対して降順ソートをすると、9時が上に来てしまう。なので、数字を扱う場合…

Illegal mix of collations エラーについて2

以前も同様の記事を書いたけど、また出くわしたのでメモしておく。 SELECT * FROM users WHERE CONCAT( sei, mei ) LIKE '%姓名%'; このようなSQLで姓名で検索をしようとしたら、「Illegal mix of collations 」が発生。原因は、 users.sei -> utf8_general_…

バイナリログをsqlに変換する方法

mysqlbinlog --no-defaults mysql-bin.000019 > /root/20130806/bin_000019.sql※--no-defaultsをつけないと'unknown variable'エラーが出るsqlに変換すれば実行日時も分かるため解析に利用できる

mysqlでselectの結果を一括してinsertしたい場合

下記でusersのデータ分すべてuser_infosにinsertが可能 insert ignore into user_infos ( id, user_id, name, deleted, created, modified ) select NULL, id, NULL, deleted, NOW(), NOW() from users order by id asc; 参考サイト [mysql] INSERT 〜 SELEC…

mysqlは、select時にgroup by で指定した列以外の値を保証しない。

無駄に悩むくらいならSQL2回に分けたほうがよほど効率的。参考サイト GROUP BYで集約されるときのソートを変えたい | MySQLのQ&A【OKWave】

MySQLにてselect結果でupdateする

テーブルAのカラム同士を結合して、同じAテーブルの別カラムを更新するsql update users as u, (select id, concat(lpad(c_id, 6, '0'), '_', mobile_number) as i from users) as u2 set u.api_id = u2.i where u.id = u2.id;

Illegal mix of collations エラーについて

collate(照合順序)の異なるテーブル同士をjoinしようとしたら↓のエラーがでたIllegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='どうやらcollateの違うテーブル同士は結合できないらしい。仕方ないの…

レプリケーションで Slave_IO_Running: No になった原因について

昨日MySQLのレプリケーション設定をして今日ステータス確認してみたら、Slave_IO_Running: Noでレプリケーションが止まっていた。エラーログには下記のように出ており、どうやらmax_allowed_packetの値が小さかったらしい。max_allowed_packet=16Mに設定 130…

sqlで日付型を条件にする際の注意

忘れていたのでメモ。timestamp,datetime型のカラムに対しての条件では、必ず hh:mm:ssまで含めること。例) select * from statement where date_col >= '2013-04-05 00:00:00' and date_col hh:mm:ss を省略すると意図したデータが取得できない。