カテゴリー: Database

Jsonでリクエストされた漢字の時刻を秒数に変換して MySQL に登録する必要あり作ってみましたが、
Javascriptであらかじめ変換してからリクエストすることにしたので、使ってません。
動作確認出来てないですが、必要な方はご利用下さい。

CREATE DEFINER=`root`@`localhost` FUNCTION `fc_intsecond_fromkanji`(
	`kanji` varchar(20)
)
RETURNS int(11)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '漢字時間文字列整数秒数取得'
BEGIN

DECLARE CNT_SEP TINYINT DEFAULT 0;
DECLARE HMSTXT VARCHAR(20) DEFAULT '';
DECLARE FMTTXT VARCHAR(20) DEFAULT '';
DECLARE CHKNUM TINYINT DEFAULT 0;
DECLARE TP     VARCHAR(3) DEFAULT '';
DECLARE H SMALLINT DEFAULT 0;
DECLARE M TINYINT DEFAULT 0;
DECLARE S TINYINT DEFAULT 0;


SET HMSTXT = REPLACE(REPLACE(REPLACE(kanji, '時間', 'H'), '分', 'M'), '秒', 'S');
SET FMTTXT = REPLACE(REPLACE(REPLACE(kanji, '時間', ':'), '分', ':'), '秒', '');

SET TP = 
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(HMSTXT, '0', ''),
'1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', '')
;


insert into tst (fld0, fld1) values (HMSTXT, TP);

IF (TP = 'HMS') THEN
  SET H = CAST(fc_delim_idxtxt(FMTTXT, 1, ':') AS UNSIGNED);
	SET M = CAST(fc_delim_idxtxt(FMTTXT, 2, ':') AS UNSIGNED);
	SET S = CAST(fc_delim_idxtxt(FMTTXT, 3, ':') AS UNSIGNED);
END IF;

/**/ 
IF (TP = 'HS') THEN
  SET H = CAST(fc_delim_idxtxt(FMTTXT, 1, ':') AS UNSIGNED);
	SET S = CAST(fc_delim_idxtxt(FMTTXT, 2, ':') AS UNSIGNED);
END IF;
IF (TP = 'HM') THEN
	SET H = CAST(fc_delim_idxtxt(FMTTXT, 1, ':') AS UNSIGNED);
	SET M = CAST(fc_delim_idxtxt(FMTTXT, 2, ':') AS UNSIGNED);
END IF;
IF (TP = 'MS') THEN
  SET M = CAST(fc_delim_idxtxt(FMTTXT, 1, ':') AS UNSIGNED);
	SET S = CAST(fc_delim_idxtxt(FMTTXT, 2, ':') AS UNSIGNED);
END IF;

/**/ 
IF (TP = 'H') THEN
	SET H = CAST(fc_delim_idxtxt(FMTTXT, 1, ':') AS UNSIGNED);
END IF;
IF (TP = 'M') THEN
	SET M = CAST(fc_delim_idxtxt(FMTTXT, 1, ':') AS UNSIGNED);
END IF;
IF (TP = 'S') THEN
  SET S = CAST(FMTTXT AS UNSIGNED);
END IF;

	 
RETURN H * 3600 + M * 60 + S;


END

MySQLの5.7から8に移行中での出来事で、ストアドが2秒程度で結果が返ってたのが、20秒程度かかってしまうので、

先日行った OSC2019 Tokyo Fall でMySQLのブースにいた人に聞いてみて、直してみると、改善できました。

全角文字を扱わない場合、文字コードは指定しない方がいいです。

< Old >

| fc_chgdvessel | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` FUNCTION `fc_chgdvessel`(
`vslnm` varchar(50)
) RETURNS varchar(36) CHARSET utf8
COMMENT '船名記号スペース変換関数'
begin

return REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(vslnm, ' ', ''), '-', ''), '.', ''), '_', ''), '(', ''), ')', ''), '.', '');

end | utf8mb4 | utf8mb4_general_ci | utf8_general_ci |

< New >

| fc_chgdvessel | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` FUNCTION `fc_chgdvessel`(
`vslnm` varchar(50)
) RETURNS varchar(36)
COMMENT '船名記号スペース変換関数'
begin

return REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(vslnm, ' ', ''), '-', ''), '.', ''), '_', ''), '(', ''), ')', ''), '.', '');

end | utf8mb4 | utf8mb4_general_ci | utf8_general_ci |

MySQL即効クエリチューニング とにかくMySQLを速くしたい人へ!プロのMySQLデータベース管理者から学ぶパフォーマンスチューニングの極意 価格：1980円（税込、送料別) (2022/12/27時点) 楽天で購入

ここで教えて頂きました : 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

SQL 第2版 ゼロからはじめるデータベース操作【電子書籍】[ ミック ] 楽天で購入

MySQL即効クエリチューニング【電子書籍】[ yoku0825 ] 価格：1980円 (2022/12/22時点) 楽天で購入

郵政事業庁の郵便番号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 第3版 (基礎からシリーズ)／西沢 夢路 楽天で購入

3ステップでしっかり学ぶMySQL入門改訂2版 [ 山田祥寛 ] 価格：2728円（税込、送料無料) (2022/12/22時点) 楽天で購入