ここで教えて頂きました : https://yoku0825.blogspot.com/2016/02/where-in-order-by-field.html
カテゴリー: MySQL
MySQL 半角カナ => ひらがな変換関数
半角カタカナが読みにくいので、ひらがなに変換するUDFを作ってみました。
濁音、半濁音は2文字になります。
パ => は°
バ => は″
ので、下に置いた変換関数をかますと正しく変換されます。
CREATE DEFINER=`root`@`localhost` FUNCTION `fc_hankana2hira`( `hankana` VARCHAR(200) ) RETURNS varchar(200) CHARSET utf8 LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN DECLARE RESTXT VARCHAR(200) DEFAULT ''; DECLARE CFG_HANKANA VARCHAR(100) DEFAULT ''; DECLARE CFG_HIRA VARCHAR(100) DEFAULT ''; DECLARE LEN_HANKANA SMALLINT DEFAULT 0; DECLARE POS SMALLINT; DECLARE CUR_HANKANA CHAR(1); DECLARE CUR_CFGPOS SMALLINT; DECLARE CUR_HIRA CHAR(1); SET CFG_HANKANA = 'アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォャュョッ゙゚(). -_[]1234567890'; SET CFG_HIRA = CONCAT( 'あいうえおかきくけこさしすせそたちつてとなにぬねのはひふへほまみむめもやゆよらりるれろ', 'わをんぁぃうぇぉゃゅょっ″°(). -_[]1234567890' ); SET LEN_HANKANA = CHAR_LENGTH(hankana); SET POS = 1; WHILE POS <= LEN_HANKANA DO SET CUR_HANKANA = MID(hankana, POS, 1); SET CUR_CFGPOS = LOCATE(CUR_HANKANA, CFG_HANKANA); SET CUR_HIRA = MID(CFG_HIRA, CUR_CFGPOS, 1); SET RESTXT = CONCAT(RESTXT, CUR_HIRA); SET POS = POS + 1; END WHILE; RETURN fc_hankanazen_dakuon_modify(RESTXT); END
CREATE DEFINER=`root`@`localhost` FUNCTION `fc_hankanazen_dakuon_modify`( `hiraorg` VARCHAR(200) ) RETURNS varchar(200) CHARSET utf8 LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '半角カナから全角ひらがな変換した後の濁音半濁音補正' BEGIN DECLARE RESTXT VARCHAR(200) DEFAULT ''; SET RESTXT = REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( hiraorg, 'か″', 'が'), 'き″', 'ぎ'), 'く″', 'ぐ'), 'け″', 'げ'), 'こ″', 'ご'), 'さ″', 'ざ'), 'し″', 'じ'), 'す″', 'ず'), 'せ″', 'ぜ'), 'そ″', 'ぞ' ); SET RESTXT = REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( RESTXT, 'た″', 'だ'), 'ち″', 'ぢ'), 'つ″', 'づ'), 'て″', 'で'), 'と″', 'ど'), 'は″', 'ば'), 'ひ″', 'び'), 'ふ″', 'ぶ'), 'へ″', 'べ'), 'ほ″', 'ぼ' ); SET RESTXT = REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( RESTXT, 'は°', 'ぱ'), 'ひ°', 'ぴ'), 'ふ°', 'ぷ'), 'へ°', 'ぺ'), 'ほ°', 'ぽ' ); RETURN RESTXT; END
MySQL LOAD DATA LOCAL INFILE で特定列を指定してインポートする
郵政事業庁の郵便番号CSVを自分の MySQL のテーブルにインポートすることになり、必要な列だけを選べないかと探してたところ、(今まで MySQL のバルクインサートでは、全列そのまんまインポートしかしたことがなかったので)
CSVの列指定は、 @1, @3, @6 とかで、列位置指定
MySQL は、
SET
JIS_ORG = @1,
ZIP_ORG = @3,
K_NAME = @9,
とかでマッピングします。
LOAD DATA LOCAL INFILE '/????/?????.csv' INTO TABLE mst_zip FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (@1, @2, @3, @4, @5, @6, @7, @8, @9, @10, @11, @12, @13, @14, @15) SET JIS_ORG = @1, ZIP_ORG = @3, K_NAME = @9, H_NAME_ORG = @6, K_JIS = @8, H_JIS_ORG = @5 ;
ここで教えていただきました : http://jigsaw.hatenablog.jp/entry/2013/06/12/113016
MySQL || で SELECT列の連結
他のデータベースからの移行で、MySQLのSELECT列連結で || が使えないものかと思い、調べてると方法がわかりました。
sql_mode に、PIPES_AS_CONCAT を加えると出来ます。
[ my.cnf 例 ]
sql_mode='NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT'
[ SELECT結果例 ]
mysql> select concat(host,user,plugin) from mysql.user where user = 'root' and host = 'localhost'; +------------------------------------+ | concat(host,user,plugin) | +------------------------------------+ | localhostrootmysql_native_password | +------------------------------------+ 1 row in set (0.00 sec) mysql> select host || user || plugin from mysql.user where user = 'root' and host = 'localhost'; +------------------------------------+ | host || user || plugin | +------------------------------------+ | localhostrootmysql_native_password | +------------------------------------+ 1 row in set (0.00 sec)
ここで教えていただきました : http://proengineer.internous.co.jp/content/columnfeature/6836#section200
MySQL 8 CTE を試す
VirtualBox の CentOS7 に入れた MySQL 8 で CTE を試してみました。
あまり参考にはならず、利用効果少ないですが、
[ 利用例 ]
貼ってから気づいたのですが、プラグインの Simple Code Highlighter が対応してなく、with が太字になってないです。
with cnttbl (ld, dc, cnt)
as (
select
d, dc, count(id) as cnt
from
sch_export_source
group by
ld, dc
)
select
dc,
pp.CD_CNTRY,
c.CD_REGION,
sum(Tomakomai) as Tomakomai,
sum(Sendai) as Sendai,
sum(Tokyo) as Tokyo,
sum(Chiba) as Chiba,
sum(Yokohama) as Yokohama,
sum(Shimizu) as Shimizu,
sum(Niigata) as Niigata,
sum(Toyama) as Toyama,
sum(Nagoya) as Nagoya,
sum(Yokkaichi) as Yokkaichi,
sum(Osaka) as Osaka,
sum(Kobe) as Kobe,
sum(Mizushima) as Mizushima,
sum(Hiroshima) as Hiroshima,
sum(Matsuyama) as Matsuyama,
sum(Moji) as Moji,
sum(Hakata) as Hakata,
sum(Kagoshima) as Kagoshima,
sum(Naha) as Naha
from
(
select
dc,
case when (ld = 'Tomakomai') then cnt else 0 end as Tomakomai,
case when (ld = 'Sendai') then cnt else 0 end as Sendai,
case when (ld = 'Tokyo') then cnt else 0 end as Tokyo,
case when (ld = 'Chiba') then cnt else 0 end as Chiba,
case when (ld = 'Yokohama') then cnt else 0 end as Yokohama,
case when (ld = 'Shimizu') then cnt else 0 end as Shimizu,
case when (ld = 'Niigata') then cnt else 0 end as Niigata,
case when (ld = 'Toyama Shinko') then cnt else 0 end as Toyama,
case when (ld = 'Nagoya') then cnt else 0 end as Nagoya,
case when (ld = 'Yokkaichi') then cnt else 0 end as Yokkaichi,
case when (ld = 'Osaka') then cnt else 0 end as Osaka,
case when (ld = 'Kobe') then cnt else 0 end as Kobe,
case when (ld = 'Mizushima') then cnt else 0 end as Mizushima,
case when (ld = 'Hiroshima') then cnt else 0 end as Hiroshima,
case when (ld = 'Matsuyama') then cnt else 0 end as Matsuyama,
case when (ld = 'Moji') then cnt else 0 end as Moji,
case when (ld = 'Hakata') then cnt else 0 end as Hakata,
case when (ld = 'Kagoshima') then cnt else 0 end as Kagoshima,
case when (ld = 'Naha') then cnt else 0 end as Naha
from
/* この部分を CTE にしてみる */
/*(
select
ld, dc, count(id) as cnt
from
sch_export_source
group by
ld, dc
)a*/
cnttbl a
) b
left outer join mst_ppcnv pp on (b.dc = pp.NM)
left outer join mst_cntry c on (pp.CD_CNTRY = c.CD_CNTRY)
group by
dc, CD_CNTRY,
CD_REGION
order by
CD_CNTRY, dc;
HeidiSQL を 10.1.0.5464 アップデートして
MySQL でいつも使ってる HeidiSQL の 10.1.0.5464 が新リリースされたようなので、早速インストールしてみたところ、デフォルトで予約語が大文字変換されるようになっていて、(ひょっとして9.5系の設定にもあったのかも知れないが、アップデート時環境設定は引き継がれるので、新たな設定だと思う)
便利な気がしたものの、SQL文の大文字、小文字は、ケースバイケースで使い分けてるので、チェックをはずしました。
詳しくはリリースノートを見たら、書いてある気がしますので、後で見ておきます。
< Ver10.1.0.5464 環境設定 >
MySQL データベースサイズストアド
mysql のコマンドで、テーブルの定義や細かいステータスは見れますが、ふと収容先の全データベースのサイズを見る必要があったので、自作してます。
名前は、コマンド用なので短くして、引数なしにしてます。
[ DDL ]
CREATE DEFINER=`root`@`localhost` PROCEDURE `dbsize`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '簡単データベースサイズ'
BEGIN
SELECT
table_schema,
sum(data_length) /1024/1024 + sum(index_length) /1024/1024 AS total_mb,
sum(data_length) /1024/1024 AS data_mb,
sum(index_length) /1024/1024 AS index_mb
FROM
information_schema.tables
GROUP BY
table_schema
ORDER BY
sum(data_length + index_length) DESC;
END
[ 利用例 ]
mysql> call dbsize; +--------------------+---------------+---------------+---------------+ | table_schema | total_mb | data_mb | index_mb | +--------------------+---------------+---------------+---------------+ | ?????????? | 4028.40940857 | 2589.96604919 | 1438.44335938 | | ?????????? | 3389.75976753 | 2149.07226753 | 1240.68750000 | | ?????????? | 1720.67187500 | 812.59375000 | 908.07812500 | | mysql | 11.26994324 | 10.99552917 | 0.27441406 | | ?????? | 3.45312500 | 2.07812500 | 1.37500000 | | information_schema | 0.15625000 | 0.15625000 | 0.00000000 | | sys | 0.01562500 | 0.01562500 | 0.00000000 | | performance_schema | 0.00000000 | 0.00000000 | 0.00000000 | +--------------------+---------------+---------------+---------------+ 19 rows in set (0.27 sec) Query OK, 0 rows affected (0.27 sec)
MyBatis MySQL 開発環境でのプーリング接続で、Too many connections の解消
本番環境では普通に JNDI のプーリングが効いて、接続数過多のエラーはまずは発生しないですが、NetBeansの開発環境で JNDI が使えないので (管理人が知らないだけなのかも)、MyBatis の設定は、MyBatis のPOOL を使ってます。
<environment id="?????">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${msdriver}"/>
<property name="url" value="${msurl}"/>
<property name="username" value="${msusername}"/>
<property name="password" value="${mspassword}"/>
<!-- maybe not affect -->
<property name="poolMaximumActiveConnections" value="1000" />
<property name="poolMaximumIdleConnections" value="1000" />
<!--<property name="maxActive" value="300" />-->
<!--<property name="maxWait" value="5000" />-->
</dataSource>
</environment>
正常にプーリングが効くはずなんですが、よく MySQL の JDBC が Too many connections でエラーを起こします。
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1014)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1110)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2465)
at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2306)
... 55 more
MySQL の my.ini (Windowsなので) の最大 300 にしていたのを、試しに 1000 に変更して、エラーの発生する処理 (連続1200レコードほどの INSERT) を行ったところ、
何故か、エラーなしに正常に処理が完了出来ました。
# The maximum amount of concurrent sessions the MySQL server will # allow. One of these connections will be reserved for a user with # SUPER privileges to allow the administrator to login even if the # connection limit has been reached. max_connections=1000
処理中、プロセス数を確認してみると、500位まで接続数が上昇し続け、アイドルなのが捨てられ、1桁に減りました。
MyBatis の問題か、connecter/J の問題かはわかりませんが、長年の??が解消しました。
MySQL mysqlコマンド (接続ステータス)
接続セッション一覧 : show processlist;
mysql> show processlist; +-------+-----------------+-----------------+----------+---------+------+-----------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------+-----------------+-----------------+----------+---------+------+-----------------------------+------------------+ | 1 | event_scheduler | localhost | NULL | Daemon | 9 | Waiting for next activation | NULL | | 67692 | root | localhost | ????? | Query | 0 | starting | show processlist | | 68108 | root | 127.0.0.1:43480 | ????? | Sleep | 21 | | NULL | | 68224 | root | 127.0.0.1:43712 | ???????? | Sleep | 29 | | NULL | | 68408 | root | 127.0.0.1:45742 | ???????? | Sleep | 24 | | NULL | +-------+-----------------+-----------------+----------+---------+------+-----------------------------+------------------+ 5 rows in set (0.00 sec)
接続セッション数 : show status like ‘Threads_connected’; または SELECT COUNT(*) FROM information_schema.PROCESSLIST;
mysql> show status like 'Threads_connected'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 4 | +-------------------+-------+ 1 row in set (0.01 sec)
mysql> SELECT count(*) FROM information_schema.PROCESSLIST; +----------+ | count(*) | +----------+ | 5 | +----------+ 1 row in set (0.00 sec)
最大接続設定 : show global variables like ‘max_connections’;
mysql> show global variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | ??? | +-----------------+-------+ 1 row in set (0.00 sec)
コメントを投稿するにはログインしてください。