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)