SQL
オンラインWebでブラウザで操作できるサービスを公開してくれてるものがある。https://sqlformat.org/Python で処理されている。GitHub - andialbrecht/sqlparse: A non-validating SQL parser module for Pythonドキュメント https://sqlparse.readthedocs.…
MySQL で PostgreSQL の generate_series 関数と同じことをしようとすると、 SET @num:= 0; SELECT @num:=@num+1 FROM `information_schema`.COLUMNS LIMIT 4; でもこれでは SET 文と２つの文になってしまって mybatis での実行では都合が悪い。 SELECT a.VC…
PostgreSQL で、連番や、日付の並びなどSQLクエリ結果として求めるのに便利なのが、 generate_series 関数１～４を ’ 1' ～ ' 4' で出力する SELECT TO_CHAR(GENERATE_SERIES(1, 4), '9999') AS UCD １～４を ’0001' ～ '0004' で出力する SELECT TO_CHAR(GE…
PostgreSQL の CURVAL() や、LASTVAL() で、シーケンスから求められるものではなく、 UUID のような型で Primary Key を構成するテーブル挿入直後のキーを取得したい。RETURNING を使う。insert文の次の書式 INSERT INTO テーブル VALUES (...) RETURNING PK…
PostgreSQL で MySQL の last_insert_id() と同様の機能、LASTVAL() （例） CREATE TABLE items ( id serial not null primary key, name varchar(128) not null ); 実行 INSERT INTO items (name) VALUES (‘Lemon’); SELECT LASTVAL(); でも、 www.postgres…
SELECT して存在しない時だけ、INSERT を行うという操作は、 2回のSQL実行文を流す＝コードすることになる。PostgreSQL の CONFLICT の DO UPDATE の条件 WHERE句を 結果 False にすれば、すでにレコードが存在すれば、UPDATEが走らないことになる。よくクエ…
かなり昔、 oboe2uran.hatenablog.comを書いた。 constraint を書かない時の UPSERT、Primary Key しか書いていない時の UPSERT の書き方は、 前回の投稿のテーブルで、id が Primary Keyである場合、、、 前回の投稿 INSERT INTO sample (id, `point`, pric…
PostgreSQL の VACUUM は、いつやるべきか？不要になったタプルが増えた時に行うべき。 SELECT relname, n_live_tup, n_dead_tup, CASE n_dead_tup WHEN 0 THEN 0 ELSE round(n_dead_tup*100/(n_live_tup+n_dead_tup) ,2) END AS ratio FROM pg_stat_user_ta…
サンプル、 DELIMITER // DROP TABLE IF EXISTS sakila.t_item // CREATE TABLE t_item( id INT NOT NULL AUTO_INCREMENT , name VARCHAR(40) NOT NULL , message JSON DEFAULT NULL , PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_…
昔、MySQL で、複数のTIMESTAMP で DEFAULT CURRENT_TIMESTAMP を宣言しようとして、 DELIMITER // DROP TABLE IF EXISTS branch; // CREATE TABLE branch ( id INT NOT NULL AUTO_INCREMENT , branch_name VARCHAR(64) NOT NULL , created_at TIMESTAMP NOT…
MySQL AUTO_INCREMENT がMAXを超える時、どんなエラーコードだっけ？ と忘れていたので、簡単に用意して実行した結果は、INSERT の発生させる AUTO_INCREMENT のエラー Error Code: 1062. Duplicate entry '2147483647' for key 'PRIMARY' INTのMAX 21474836…
随分前に、MySQL の UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT のメモを書いていた。 https://oboe2uran.hatenablog.com/entry/2018/02/01/160016 CREATE TABLE branches ( id INT(4) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT , branch_name VARCHAR(60…
MySQL で、外部キーのあるテーブルがあるスキーマを mysqldump で採取した SQLソースを そのままリストアに使うと Foreign key constraint is incorrectly formed となる場合がある。そんな場合の解決方法、、 SET FOREIGN_KEY_CHECKS=0; をＳＱＬの先頭に記…
久しぶりに Oracle 使用のプロジェクトで、 ＳＱＬで、WHERE句で結合演算子(+) を書いてるのを見かけた。。。あれ！、たしか Oracle 12c で奨励されなくなったのでは？！。。。https://docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF30046引用す…
先日の例、 PostgreSQL の再帰SQL で要素の連結結果を求める。 - Oboe吹きプログラマの黙示録 テーブル名：division id parent_id name 1 null A 2 1 B 3 2 C 4 2 D ここから、連結文字列の結果、id と name "1" "1,2" "1,2,3" "1,2,4""A" "A,B" "A,B,C" "A,…
再帰SQL で要素の連結、どいうことかというと、 テーブル名：division id parent_id name 1 null A 2 1 B 3 2 C 4 2 D というテーブルが存在した時、再帰SQLを走らせて、 ”A” "A,B" "A,B,C" "A,B,D" のように各々のレコードに対して親→子の name列の連結＝つ…
MySQL は、8.x なら再帰SQL（ WITH RECURSIVE target AS ...）が使えるらしいが、 今回はPostgreSQL の再帰SQL 再帰SQLクエリを実行するテーブル構造 テーブル名：divison id プライマリーキー parent_id 親を指すID name 名称 このテーブル、指定のID(id=1)…
MySQL では、更新時に対象レコードの列の値をカウントＵＰ更新は以下のようにできる。 UPDATE uranus SET icount = icount + 1 WHERE id = 1 更新するレコードが存在しなければ カウント初期値＝０をINSERT 更新するレコードが存在すればカウントＵＰ とした…
MySQL で id など AUTO_INCREMENT を用意するとき、 ０埋めで作成されるようにするには、桁数指定で、UNSIGNED ZEROFILL を付ける例）4桁 CREATE TABLE branches ( id INT(4) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT , branch_name VARCHAR(60) NOT NULL…
他人が構築したＤＢを引き継ぐのは、嫌なもので状況を把握しないまま、 MySQL ストアドファンクションを追加したら、 Error Code: 1419. You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bi…
PostgreSQL に馴染みがなく、アップサートあるいは、SERIAL に困惑している。 MySQL には、AUTO INCREMENT があり、例えば、以下のようなテーブル CREATE TABLE sample ( id INT NOT NULL AUTO_INCREMENT, point INT, price INT, PRIMARY KEY (id) ) ENGINE=…
MySQL で UPSERT をするとき、REPLACE を使うべきか？ INSERT のオプション ON DUPLICATE KEY UPDATE を使うべきか？REPLACE は、キー重複する行を削除してから INSERT することを考えると、ON DUPLICATE KEY UPDATE の方が 更新する列を指定できるから良い…
たまにしか書かない、GROUP_CONCAT の書式をよく忘れるのでメモ。 GROUP_CONCAT( 列名 [ ORDER BY 順序つける列名 ] [ SEPARATOR 区切り文字] ) 注意しなければならないのは CONCAT対象の列の値が NULL の場合、 SEPARATOR を指定しても区切り文字もつかず全…
MySQL で ストアドFUNCTION 作成で、 Error Code: 1418. This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators…
MySQL ダンプを復元するとき、通常は、 mysql -u ユーザ名 -p データベース名 < ダンプファイル でも、Linux の MySQLでダンプしたものを Windows で復元を同じコマンドで実行してしまうと、 Unknown command '\'' と、エラーになってしまう。当然と言えばそ…
MySQL ワークベンチで、時間かかるプロシジャなどを実行していると Error Code: 2013 Lost connection to MySQL server during query 600.135 sec となってしまうことがある。 こういう場合、設定でSQLEditer の中のMySQL Session のDBMS connection keep-al…
MySQL のストアドFUNCTION として請求月とサイト（支払い期限までの日数）から、 支払期限日（DATE型）を求めるものを作ってみました。 請求が末締めで、末日からのサイト（日数）を求めるものとします。 サイト日数の仕様は、１ヶ月を３０日として定義して…
MySQL をＪａｖａで使用してTIMESTAMP や DATETIME をＪａｖａのオブジェクトに変換する時、データに０が入ってたりすると、 Value '0000-00-00' can not be represented as java.sql.Timestamp JDBC SQLException を発生してしまう。Java用のMySQLコネクタ…
ＭｙＢａｔｉｓで、MUTIPLE INSERT を実行するのは、foreach を使います。 例） //テーブルのデータ定義クラス→Item public class Item implements Serializable{ public String item; public int price; public Item(){ } } // mybatis の SQLセッション、…
MySQL の GROUPCONCAT 連結した文字数に制限があった。 思わねところでハマった。 show variables like 'group_concat_max_len'; で確認すると、１０２４ これがデフォルト！ うっかりすると超えてしまう。 環境変数：group_concat_max_len をセットすれば良…
