ドゥアイネットのプログラマー、湯川が開発日記を綴ります。

CALENDAR
S M T W T F S
1234567
891011121314
15161718192021
22232425262728
293031    
<< December 2019 >>
ARCHIVES
CATEGORIES
msqlダンプで
今日は作業でMysqlでダンプをとって取り込みました。
ダンプをとるまではよかったのですが、取り込みの際にエラーとなりました。
エラーメッセージの内容的に、SQL一文が長すぎて、
一度の読込のmaxを超えているといった感じでした。

ダンプ内のテーブルデータが多いのと、
ダンプに書き出されるSQLがこんな感じになっているので
SQLが長くなっているようです。

insert into table1 values
('データ1','データ2'),('データ1','データ2'),('データ1','データ2')・・・

SQLがこうなれば、読込エラーにならないはずです。
insert into table1 values ('データ1','データ2');
insert into table1 values ('データ1','データ2');
insert into table1 values ('データ1','データ2');
・・・

そこでダンプの取り方を調べてみました。
そしたら「net_buffer_length」というのがありました。
これを指定すると、出力されるダンプの一行の最大値を指定できるようです。

mysqldump --opt -O net_buffer_length=4096 -u userid -p dbname > /tmp/dmp.sql

これで出力したら1レコード、一行な感じで
ダンプがとれて、無事取り込むことができました。
| Mysql | 17:32 | comments(0) | - |
MysqlのRollup
この前、新しくなった市立図書館に行きました。
広くてきれいでした。
絵本はたくさんありましたが、技術系の本は
思ったよりありませんでした(借りられてるだけかもしれないですが)。

そこで「SQL HACKS」という本を借りました。
そのうち返さないといけないので気になった内容をここにメモしておきます。

Mysqlなどで使える「Rollup」というのが書かれていました。
明細と集計がSELECTだけでとれるといったものです。
このようなデータがあったとして
画像

こんな感じで、
それぞれの金額の明細と合計が一度に求められます。
画像

select
col_item,col_num,sum(col_price)
from rolluptest
group by col_item,col_num with rollup;

最後に「with rollup」をつけるだけです。
なんか使えそう、でも使う機会はないかもと
思いつつ一応メモしておきます。
| Mysql | 18:47 | comments(0) | - |
mysqlのテーブルコピー
mysqlでテーブルをコピーしたかったので調べました。

まずは
「create table テーブル名 as select * from コピーするテーブル名」
を試しました。
データも一度にコピーされていい感じ。
と思ったのですが、主キーなどの構造が設定されません。

次に試したのが
「create table テーブル名 LIKE コピーするテーブル名」
これだと主キーなどの構造は設定されます。
が、データは入りません。

なので、「create table テーブル名 LIKE コピーするテーブル名」
でテーブルを作成して、
insert文でコピー元のテーブルからデータを写しました。

最初の「as select ・・」で一度に
構造までコピーされてほしいような気がします。
主キーがあったのにない状態でコピーされても。。
| Mysql | 17:16 | comments(3) | - |
NavicatのMysqlツール
Navicat8 Lite for MySQLというソフトがあったので
ダウンロードしてみました。

起動もけっこう速いですし、データも普通に見れます。
クエリも実行して、それを保存することもできます。

気に入ったので今度からこれを使おうと思います。
最初日本語が読めなかったので設定しました。
「詳細」タブのエンコードで「japanese EUC」を選んだら読めるようになりました。
忘れそうなのでここにメモしときます。

画像
| Mysql | 18:40 | comments(0) | - |
SQLを分解
いま実行できるSQL文の制御を作成しています。
SQLは自由に入力できるのですが、
使ってはいけないテーブルを各ユーザごとに設定してあるので
そのテーブル名を含むSQLは実行させないようにしています。

単純にfind-stringメソッドで判断すると
select * from tbl1
where col1 = 'table1'

とかのときに、table1がつかってはいけないテーブル名だったときに
引っかかってしまいます。
また、「col」という使ってはいけないテーブルがあっても
引っかかります。

なので、SQL文を各単語にわけて、テーブル名が含まれていないか
判断することにしました。

v_checksqlに確認するSQLが入っているとして
まず、単語以外でつかわれる記号(=とか)、を空白に変換します。

set v_checksql = {v_checksql.replace-clone "¥n", " "}
set v_checksql = {v_checksql.replace-clone ">=", " "}
set v_checksql = {v_checksql.replace-clone "<=", " "}
set v_checksql = {v_checksql.replace-clone "<", " "}
set v_checksql = {v_checksql.replace-clone ">", " "}
set v_checksql = {v_checksql.replace-clone "=", " "}
set v_checksql = {v_checksql.replace-clone "(", " "}
set v_checksql = {v_checksql.replace-clone ")", " "}
set v_checksql = {v_checksql.replace-clone "!", " "}

それをsplitして配列にいれます。
let v_split-sql:StringArray = {v_checksql.split split-chars =" "}

あとはこれをfor文でまわして、使えないテーブルが含まれていないかを判断しました。
なんか力業な感じですけどうまくいきました。
| Mysql | 19:02 | comments(0) | - |
LAST_INSERT_ID
MysqlのDB処理で、Insertを行った後、AUTO_INCREMENTの
フィールドにいくつが入ったかをとる処理を作成しました。

最初はInsertの後にmaxでとろうかと思ったのですが、
同じ処理をほぼ同時に実行した場合、違う値(次の値とか)に
なってしまいます。

で、ちょっと調べたら「LAST_INSERT_ID()」が出てきました。

以下引用です。
生成された最後のID は、接続ごとにサーバで維持される。
したがって、この関数から個々のクライアントに返される値は、
そのクライアントによって生成された最新の AUTO_INCREMENT値である。

要は、自分のInsertで生成されたIDが返ってくるということみたいです。
便利ですね。
| Mysql | 15:48 | comments(1) | - |
show create table文
今日の開発で一時テーブルを作成する処理がありました。

別データベースのテーブル構造と同じものをつくる処理です。
同じデータベースだったらcreate temporary table select・・・
とできるのですが、そうもいきません。
テーブル定義書をながらcreate文をコーディングするのは
時間もかかるし、ミスする可能性大です。

そこで「show create table」を使ってみました。
show create table テーブル名;で実行すると
create文が結果として返ってきます。

時間もかからないし、ミスもないです。
極力手作業は避けたいと思います。
| Mysql | 18:50 | comments(0) | - |
joinとdelete文
SQLのdelete文でjoinを使って削除の条件をつないといけない処理がありました。
できるかどうかわからないので、とりあえず実行してみました。

tblAのデータを削除、条件はtblAのcol1とtblBのcol1が
一致するもので、かつtblBのcol2が'aaaa'のデータ。

delete from tblA
inner join tblB
on tblA.col1 = tblB.col1
where tblB.col2='aaaa'

SQLエラーでした。
次にネットを検索。
ありました。同じ様なことをやっている例が。
deleteとfromの間に削除対象テーブル名を書けばエラーにならずにデータ削除できました。

delete tblA from tblA 
inner join tblB
on tblA.col1 = tblB.col1
where tblB.col2='aaaa'

結合して削除、意外とやったことなかったです。
| Mysql | 15:49 | comments(3) | - |
SQLの速度
SQLは書き方で速度が変わります。
とくにMysqlでサブクエリを使うのと使わないのとでは
ものすごい差がでます。

データ件数が少ないと、そう差はでないのですが
何万件とかなってくるとえらいことになります。

tbl1に5万件、tbl2に1万件くらいデータが入っているとすると
このSQLの場合、すぐに結果が返ってきます。1秒未満です。
select tmp.col1
from tbl1
inner join tbl2
on tbl2.key = tbl1.key
where tbl2 col1 ='xxx'
limit 0,30

でも以下のようにサブクエリを使うと・・・
select tmp.col1
from tbl1
inner join
(select key from tbl2
where tbl2 col1 ='xxx') tmp
on tbl2.key = tbl1.key
limit 0,30

結果がもどるまで「209秒」かかりました。

他にもorder byを使うか、使わないかでも少し差が出ます。

これからは結合順序とかも考えてSQLを書きたいと思います。
| Mysql | 19:22 | comments(0) | - |
CASEを使った集計SQL
今回はまともな技術ネタです。

SQLの集計についてなのですが、
この方法をネットの記事で読んだときはけっこう感動しました。

こんなテーブルとデータがあったとして

金額テーブル
コード 金額
001   500
001   300
002   800
003   500
002   700
コードごとに金額を合計するとします。
普通にやると
SELECT SUM(金額) FROM 金額テーブル WHEREコード = '001'
SELECT SUM(金額) FROM 金額テーブル WHEREコード = '002'



と、ひとつずつ求めるのですが、これを一つのSQLで書く方法です。

このように「CASE」を使って書くと

SELECT
SUM( CASE WHEN コード = '001' THEN 金額 ELSE 0 END),
SUM( CASE WHEN コード = '002' THEN 金額 ELSE 0 END),
SUM( CASE WHEN コード = '003' THEN 金額 ELSE 0 END)
FROM 金額テーブル

コードごとの合計を横並び1行で取得できます。
800 | 1500 | 500

今回こういった集計処理があって、テーブルに列を追加するかどうか
考えていたときに思い出しました。
テストで15000件のデータを集計してみたのですが、すぐに結果が返ってきました(296 ms)。
画像
これを使うのと使わないのではロジックが大きくかわります。
| Mysql | 13:46 | comments(0) | - |
<< | 2/2PAGES |