カテゴリー: MySQL

ここで教えて頂きました : https://yoku0825.blogspot.com/2016/02/where-in-order-by-field.html

続きを読む →

半角カタカナが読みにくいので、ひらがなに変換する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(
'あいうえおかきくけこさしすせそたちつてとなにぬねのはひふへほまみむめもやゆよらりるれろ',
'わをんぁぃうぇぉゃゅょっ″°（）．　－＿［］１２３４５６７８９０'
);


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

                            

郵政事業庁の郵便番号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 のコマンドで、テーブルの定義や細かいステータスは見れますが、ふと収容先の全データベースのサイズを見る必要があったので、自作してます。
名前は、コマンド用なので短くして、引数なしにしてます。

[ 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)

接続セッション一覧 : 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)