トップページに戻る    オートコンプリート

MySQLメモ


目次

001. WindowsXPと7でのPHP環境構築
002. mysqldプロセス開始、mysqlコマンドプロンプトの起動
003. データベース名とバージョンを調べる
004. Oracleのdump関数のような関数
005. 主キーのUpdate
006. Date型とDateTime型のデフォルト書式
007. 数値と文字列の相互変換
008. LPad関数で数値の0パディング
009. RollUp集計
010. 正規表現
011. Least関数とGreatest関数
012. 一か月後を求める
013. 末日を求める
014. Date_Format関数
015. Find_IN_Set関数
016. IF関数
017. IfNull関数
018. Concat_ws関数
019. Field関数
020. Elt関数
021. 他テーブルを参照してのUpdate
022. Insert文でMerge文もどき
023. Insert文で一意制約違反をIgnore
024. Replace文でDeleteしてInsert
025. Update文とDelete文でのLimit句指定
026. MySQLのBoolean型
027. count関数の複数式distinct
028. Update文でDateTime型の重複を解消
029. Auto_Incrementの調査
030. Left関数とRight関数
031. Concat関数や数値型の0割りでのnull
032. Usingを使ったJoin
033. Truncate関数での切捨て
034. コマンドラインの結果をテキストに出力
035. テーブル定義の変更のサンプル
036. テーブル定義のコンペア用のSelect文
037. SQLモードの確認
038. mysql.exeの-Dというコマンドラインオプション
039. Update文でソートキーをユニークに座標圧縮
040. GroupBy句での列番号指定
041. group_concat関数の長さ上限
042. 現在日時の表示
043. OrderByでのNullの判定
044. 文字列の指定文字列以降を抽出
045. InStr関数とFind_IN_Set関数とChar_Length関数
046. オートインクリメントと主キーの設定
047. 全部のテーブルのdrop文を作成
048. MySQL徹底入門(4版)の読書メモ


001 WindowsXPと7でのPHP環境構築

WindowsXP(SP2)で動くMySQLの最新バージョンは、5.5.62

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
Windows7 64ビットでMySQL8.0.15のインストール手順

その1
.net framework 4.5.2をインストールしたが、これは、おそらく不要

その2
WindowsUpdateでWindows7をSP1にする

その3
Microsoft Visual C++ 2015
Redistributable(X64) - 14.0.23026
vc_redist.x64.exe
をインストールする

その4
mysql-installer-community-8.0.15.0.msi
をインストールする


002 mysqldプロセス開始、mysqlコマンドプロンプトの起動

mysqldプロセス開始
D:\MySQL5.5\mysql-5.5.62-win32\bin\mysqld

mysqlコマンドプロンプトの起動
D:\MySQL5.5\mysql-5.5.62-win32\bin\mysql -D test

mysqldプロセス終了
D:\MySQL5.5\mysql-5.5.62-win32\bin\mysqladmin -u root shutdown


003 データベース名とバージョンを調べる

select database(),version();

+------------+-----------+
| database() | version() |
+------------+-----------+
| test       | 5.5.62    |
+------------+-----------+


004 Oracleのdump関数のような関数

MySQLには、ないっぽい
CTASしてdesc テーブル名で代用するしかなさそう


005 主キーのUpdate

create table TestTable(ColA int primary key);

insert into TestTable values(1),(2),(3);

update TestTable set ColA = ColA + 1;

update文中の一意制約違反であっても
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
が発生する。

update TestTable set ColA = ColA + 1 order by ColA desc;
で、降順にソートすれば防げる(競プロのDPのループの向きの感覚)


006 Date型とDateTime型のデフォルト書式

MySQLのデフォルトのDate/DateTime書式は何ですか?

MySQLのデフォルトのDateフィールド書式はYYYY-MM-DDです。
DateTime対応は日付と時間の組み合わせで、YYYY-MM-DD HH:MM:SS書式でデータを保存します。


007 数値と文字列の相互変換

create table CastCheck as
select cast('123' as Signed) as CharToNum,
       cast(123   as char(3)) as NumToChar;

desc CastCheck;
+-----------+------------+------+-----+---------+-------+
| Field     | Type       | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| CharToNum | int(3)     | NO   |     | 0       |       |
| NumToChar | varchar(3) | NO   |     |         |       |
+-----------+------------+------+-----+---------+-------+


008 数値の0パディング

select LPad('123',6,'0');

+-------------------+
| LPad('123',6,'0') |
+-------------------+
| 000123            |
+-------------------+


009 RollUp集計

select ID,group_concat(Val) as concatVal
from (select 1 as ID,'A' as Val union
      select 1      ,'B' union
      select 2      ,'C' union
      select 2      ,'D' union
      select 3      ,'E') tmp
group by ID With RollUp;

+------+-----------+
| ID   | concatVal |
+------+-----------+
|  1   | A,B       |
|  2   | D,C       |
|  3   | E         |
| NULL | A,B,D,C,E |
+------+-----------+

group by ColA,ColB,ColC With RollUp
は、Oracleの下記と同じっぽい
group by RollUp(ColA,ColB,ColC)


010 正規表現

select '123ABC' RegExp '[A-Z]+'   as tes1,
       '123ABC' RegExp '^[A-Z]+$' as tes2;

+------+------+
| tes1 | tes2 |
+------+------+
|    1 |    0 |
+------+------+

マッチパターンが部分一致すれば1を返し、マッチしなければ0を返す。

8.0でも、肯定先読み、否定先読み、肯定戻り読み、否定戻り読み
はサポートされない。

MySQL8.0で、RegExp_ReplaceやRegExp_SubStrやRegExp_InStrが追加されました。
12.5.2 Regular Expressions


011 Least関数とGreatest関数

引数にnullがあるとnullを返す。

select Least(10,20,30)    as tes1,
       Greatest(10,20,30) as tes2,
       Least(1,2,null)    as tes3,
       Greatest(1,2,null) as tes4;

+------+------+------+------+
| tes1 | tes2 | tes3 | tes4 |
+------+------+------+------+
|   10 |   30 | NULL | NULL |
+------+------+------+------+


012 一か月後を求める

select date '2019-01-31' as tes1,
       date '2019-01-31' + InterVal 1 month as tes2;

+------------+------------+
| tes1       | tes2       |
+------------+------------+
| 2019-01-31 | 2019-02-28 |
+------------+------------+


013 末日を求める

select Last_day(date '2019-02-01') as tes1,
       Date_Format(date '2019-02-01','%d') as tes2;

+------------+------+
| tes1       | tes2 |
+------------+------+
| 2019-02-28 | 01   |
+------------+------+


014 Date_Format関数

select Date_Format(Val,'%Y%m')
from (select date '2018-11-01' as Val union
      select date '2018-12-15' as Val union
      select date '2018-12-30' as Val union
      select date '2019-01-01' as Val) as tmp;

+-------------------------+
| Date_Format(Val,'%Y%m') |
+-------------------------+
| 201811                  |
| 201812                  |
| 201812                  |
| 201901                  |
+-------------------------+


015 Find_IN_Set関数

select Find_IN_Set('a','a,b,c') as tes1,
       Find_IN_Set('b','a,b,c') as tes2,
       Find_IN_Set('c','a,b,c') as tes3,
       Find_IN_Set('d','a,b,c') as tes4;

+------+------+------+------+
| tes1 | tes2 | tes3 | tes4 |
+------+------+------+------+
|    1 |    2 |    3 |    0 |
+------+------+------+------+


016 IF関数

select IF(1 = 1,'真','偽') as tes1,
       IF(1 = 2,'真','偽') as tes2;

+------+------+
| tes1 | tes2 |
+------+------+
| 真   | 偽   |
+------+------+


017 IfNull関数

--引数が2つのCoalesce関数
select IfNull(null,'nullです') as tes1,
       IfNull(   1,'nullです') as tes2;

+----------+------+
| tes1     | tes2 |
+----------+------+
| nullです | 1    |
+----------+------+


018 Concat_ws関数

select Concat_ws('---','1','2','3') as concated;

+-----------+
| concated  |
+-----------+
| 1---2---3 |
+-----------+


019 Field関数

select Field('a','a','b','c') as tes1,
       Field('b','a','b','c') as tes2,
       Field('c','a','b','c') as tes3,
       Field('d','a','b','c') as tes4;

+------+------+------+------+
| tes1 | tes2 | tes3 | tes4 |
+------+------+------+------+
|    1 |    2 |    3 |    0 |
+------+------+------+------+


020 Elt関数

select Elt(1, 'a', 'b', 'c', 'd') as tes1,
       Elt(2, 'a', 'b', 'c', 'd') as tes2,
       Elt(3, 'a', 'b', 'c', 'd') as tes3,
       Elt(4, 'a', 'b', 'c', 'd') as tes4,
       Elt(5, 'a', 'b', 'c', 'd') as tes5;

+------+------+------+------+------+
| tes1 | tes2 | tes3 | tes4 | tes5 |
+------+------+------+------+------+
| a    | b    | c    | d    | NULL |
+------+------+------+------+------+


021 他テーブルを参照してのUpdate

5.6のマニュアル Update文

create table MasterTable(
ID  int,
Val varchar(10),
primary key(ID)
)DEFAULT CHARSET=utf8;

insert into MasterTable values(1,'牛若丸'),
                              (2,'木下藤吉郎'),
                              (3,'徳川家康'),
                              (4,'明智光秀');

create table TranTable(
ID  int,
Val varchar(10),
primary key(ID)
)DEFAULT CHARSET=utf8;

insert into TranTable values(1,'源義経'),
                            (2,'豊臣秀吉');

update MasterTable,TranTable
set MasterTable.Val = TranTable.val
where MasterTable.ID = TranTable.ID;

-- JoinでonやUsingを使ってもOK
update MasterTable a
  Join TranTable b Using(ID)
set a.Val = b.val;

select * from MasterTable;
+----+----------+
| ID | Val      |
+----+----------+
|  1 | 源義経   |
|  2 | 豊臣秀吉 |
|  3 | 徳川家康 |
|  4 | 明智光秀 |
+----+----------+

本番環境のデータ編集で誤更新が怖い場合は、
更新テーブルを明示する、下記の相関サブクエリを使ったUpdateのほうが良い
update MasterTable a
set a.Val = (select b.Val
               from TranTable b where b.ID = a.ID)
where exists(select 1 from TranTable b where b.ID = a.ID);


022 Insert文でMerge文もどき

5.6のマニュアル Insert文

create table MergeTest(
ID  int,
Val varchar(10),
primary key(ID)
)DEFAULT CHARSET=utf8;

insert into MergeTest values(1,'牛若丸'),
                            (2,'木下藤吉郎');

insert into MergeTest(ID,Val)
values(1,'源義経'),
      (2,'豊臣秀吉'),
      (3,'徳川家康'),
      (4,'明智光秀')
on duplicate key update MergeTest.Val= Values(Val);

select * from MergeTest;
+----+----------+
| ID | Val      |
+----+----------+
|  1 | 源義経   |
|  2 | 豊臣秀吉 |
|  3 | 徳川家康 |
|  4 | 明智光秀 |
+----+----------+


023 Insert文で一意制約違反をIgnore

5.6のマニュアル Insert文 より引用
重複キー違反の原因になる行を無視するには、Ignore を指定します。

create table DupkeyTest(ID int primary key);

insert Ignore into DupkeyTest values(1);
insert Ignore into DupkeyTest values(1);
insert Ignore into DupkeyTest values(2);

select Group_Concat(cast(ID as Char(10))) as Concat_Str from DupkeyTest;
+------------+
| Concat_Str |
+------------+
| 1,2        |
+------------+


024 Replace文でDeleteしてInsert

Replace構文 より引用
Replaceは、Insertとまったく同じように機能します。
ただし、テーブル内の古い行に、
PrimaryKeyまたは Uniqueインデックスに関して新しい行と同じ値が含まれている場合、
その古い行は新しい行が挿入される前に削除されます。

MySQLは、REPLACE (および LOAD DATA ... REPLACE) に次のアルゴリズムを使用します。
1. テーブルへの新しい行の挿入を試みます。
2. 主キーまたは一意のインデックスに関する重複キーエラーが発生して挿入が失敗した場合、次のことを行います。
    a. 重複キー値を含む競合している行をテーブルから削除します。
    b. テーブルへの新しい行の挿入を再試行します。

create table RepT(
ID  int,
Val varchar(10),
primary key(ID));

insert into RepT values(1,'AAAA'),
                       (2,'BBBB');
select * from RepT;
+----+------+
| ID | Val  |
+----+------+
|  1 | AAAA |
|  2 | BBBB |
+----+------+

replace into RepT values(1,'CCCC'),
                        (3,'DDDD');
select * from RepT;
+----+------+
| ID | Val  |
+----+------+
|  1 | CCCC |
|  2 | BBBB |
|  3 | DDDD |
+----+------+


025 Update文とDelete文でのLimit句指定

create table LimitTest(Val int);
insert into LimitTest values(1),(2),(3),(4),(5);

5.6のマニュアル Update文
update LimitTest set Val = 8 Order by Val desc Limit 2;
select Group_Concat(cast(Val as Char(10))) as Concat_Str from LimitTest;
+------------+
| Concat_Str |
+------------+
| 1,2,3,8,8  |
+------------+

5.6のマニュアル Delete文
delete from LimitTest Order by Val Limit 2;
select Group_Concat(cast(Val as Char(10))) as Concat_Str from LimitTest;
+------------+
| Concat_Str |
+------------+
| 3,8,8      |
+------------+


026 MySQLのBoolean型

Boolean型は、C++の論理型のように使える。

select min(Col),max(Col)
  from (select 0=1 as Col union
        select 1=1) tmp;
+----------+----------+
| min(Col) | max(Col) |
+----------+----------+
|        0 |        1 |
+----------+----------+


027 count関数の複数式distinct

count関数の複数式distinctは、1つでもnullだとカウントしない。

select count(distinct ColA,ColB) as DisCnt
  from (select 1 as ColA,null as ColB union
        select null     ,   1         union
        select null     ,null         union
        select    1     ,   1) tmp;
+--------+
| DisCnt |
+--------+
|      1 |
+--------+


028 Update文でDateTime型の重複を解消

create table UniqTimeTest(
col_ID   int,
col_Time TimeStamp);

insert into UniqTimeTest values(1,now()),
                               (2,now()),
                               (3,now());
select * from UniqTimeTest;
+--------+---------------------+
| col_ID | col_Time            |
+--------+---------------------+
|      1 | 2019-02-08 00:27:21 |
|      2 | 2019-02-08 00:27:21 |
|      3 | 2019-02-08 00:27:21 |
+--------+---------------------+

update UniqTimeTest
set col_Time = col_Time
  + InterVal (select count(*) from (select * from UniqTimeTest) tmp
               where tmp.col_ID < UniqTimeTest.col_ID ) second;
select * from UniqTimeTest;
+--------+---------------------+
| col_ID | col_Time            |
+--------+---------------------+
|      1 | 2019-02-08 00:27:21 |
|      2 | 2019-02-08 00:27:22 |
|      3 | 2019-02-08 00:27:23 |
+--------+---------------------+


029 Auto_Incrementの調査

Auto_Increment指定すると、
テーブルデータの最大値の次の値と、
テーブルのAuto_Incrementの値で、
大きいほうを採番する。

Auto_Increment型の列をValues句で指定して、任意の値をInsertすることもできる。

create table inc_test(ColA int auto_increment primary key,ColB text);

insert into inc_test(ColB) values('AAA');
insert into inc_test(ColA,ColB) values(900,'BBB');
insert into inc_test(ColB) values('CCC');
insert into inc_test(ColB) values('DDD');
insert into inc_test(ColA,ColB) values(100,'EEE');

alter table inc_test auto_increment = 12345;
insert into inc_test(ColB) values('FFF');

select * from inc_test;
+-------+------+
| ColA  | ColB |
+-------+------+
|     1 | AAA  |
|   100 | EEE  |
|   900 | BBB  |
|   901 | CCC  |
|   902 | DDD  |
| 12345 | FFF  |
+-------+------+


030 Left関数とRight関数

select Left ('ABCDE', 3) as tes1,
       Right('ABCDE', 3) as tes2;

+------+------+
| tes1 | tes2 |
+------+------+
| ABC  | CDE  |
+------+------+


031 Concat関数や数値型の0割りでのnull

concat関数は、引数にnullがあるとnullを返す。
concat_ws関数は、接続文字列にnullがあったら無視する。

MySQLで1/0のようなゼロ割りはnullとなる。

IfNull関数は、使用されているコンテキストに応じて、
数値または文字列値を返す。

select IfNull(1/0,'yes'); -- 'yes'


032 Usingを使ったJoin

select * from
(select 1 ID union select 2) a Left Join
(select 1 ID union select 3) b Using(ID);

+----+
| ID |
+----+
|  1 |
|  2 |
+----+


033 Truncate関数での切捨て

select truncate(123.456,-2) as tes1,
       truncate(123.456,-1) as tes2,
       truncate(123.456, 0) as tes3,
       truncate(123.456, 1) as tes4,
       truncate(123.456, 2) as tes5,
       truncate(123.456, 3) as tes6;

+------+------+------+-------+--------+---------+
| tes1 | tes2 | tes3 | tes4  | tes5   | tes6    |
+------+------+------+-------+--------+---------+
|  100 |  120 |  123 | 123.4 | 123.45 | 123.456 |
+------+------+------+-------+--------+---------+


034 コマンドラインの結果をテキストに出力

tee '出力先のフルパス'
select 12345;
notee


035 テーブル定義の変更のサンプル

drop table DDLTest;
create table DDLTest(OldColumnName varchar(111) not null);
desc DDLTest;

-- 列名の変更
ALTER TABLE DDLTest CHANGE COLUMN OldColumnName NewColumnName varchar(222) not null;
desc DDLTest;

-- 列の定義の変更
ALTER TABLE DDLTest MODIFY COLUMN NewColumnName varchar(333) not null;
desc DDLTest;

-- 列の追加
ALTER TABLE DDLTest ADD COLUMN TuikaColumnName int not null AFTER NewColumnName;
desc DDLTest;

-- 列の削除
ALTER TABLE DDLTest DROP COLUMN TuikaColumnName;
desc DDLTest;


日々の覚書: MySQL 8.0ではカラムのリネームに ALTER TABLE RENAME COLUMN 構文が使える


036 テーブル定義のコンペア用のSelect文

select a.TABLE_NAME,a.COLUMN_NAME, a.DATA_TYPE, a.IS_NULLABLE, a.COLUMN_DEFAULT,
       b.TABLE_NAME,b.COLUMN_NAME, b.DATA_TYPE, b.IS_NULLABLE, b.COLUMN_DEFAULT
  from      INFORMATION_SCHEMA.COLUMNS a
  Left Join INFORMATION_SCHEMA.COLUMNS b
    on b.table_schema = 'db_name'
   and b.TABLE_NAME Like 'abcdef%' -- このLike述語の条件を交換して2回実行すればOK
   and b.TABLE_NAME   = a.TABLE_NAME
   and b.COLUMN_NAME  = a.COLUMN_NAME
 where a.TABLE_NAME Like 'ghijk%' -- このLike述語の条件を交換して2回実行すればOK
   and a.table_schema = 'db_name'
   and (b.TABLE_NAME is Null
     or a.DATA_TYPE      <=> b.DATA_TYPE
     or a.IS_NULLABLE    <=> b.IS_NULLABLE
     or a.COLUMN_DEFAULT <=> b.COLUMN_DEFAULT)
order by a.COLUMN_NAME;


037 SQLモードの確認

select @@GLOBAL.sql_mode;
でSQLモードを確認できる。

NO_BACKSLASH_ESCAPES が有効だと \' は、文字としての \と' となる。
NO_BACKSLASH_ESCAPES が無効だと \' は、文字としての ' となる。


038 mysql.exeの-Dというコマンドラインオプション

mysql.exe のコマンドラインオプションで
-D test
を指定するとtestデータベースに接続できる。

4.5.1.1 mysql のオプション


039 Update文でソートキーをユニークに座標圧縮

create table UniqZaatu(ID int primary key,SortKey int);
insert into UniqZaatu values(10,  1), --  1が座圧後の値
                            (30,  1), --  2が座圧後の値
                            (90,  1), --  3が座圧後の値
                            (50,  2), --  4が座圧後の値
                            (60,  3), --  5が座圧後の値
                            ( 1,  4), --  6が座圧後の値
                            ( 2,  7), --  7が座圧後の値
                            ( 3,  8), --  8が座圧後の値
                            ( 4,100), --  9が座圧後の値
                            ( 5,110); -- 10が座圧後の値

update UniqZaatu a
   set SortKey = (select count(*) + 1
                    from (select * from UniqZaatu) b
                   where  b.SortKey < a.SortKey
                      or (b.SortKey = a.SortKey and b.ID < a.ID));

select * from UniqZaatu order by SortKey;

+----+---------+
| ID | SortKey |
+----+---------+
| 10 |       1 |
| 30 |       2 |
| 90 |       3 |
| 50 |       4 |
| 60 |       5 |
|  1 |       6 |
|  2 |       7 |
|  3 |       8 |
|  4 |       9 |
|  5 |      10 |
+----+---------+


040 GroupBy句での列番号指定

select ID,count(*) as cnt
  from (select 'A' as ID union all
        select 'B' union all
        select 'B') tmp
group by 1;

+----+-----+
| ID | cnt |
+----+-----+
| A  |   1 |
| B  |   2 |
+----+-----+

13.2.9 SELECT 構文
カラム名、カラムのエイリアス、またはカラム位置を使用して、
出力のために選択されたカラムを ORDER BY および GROUP BY 句で参照できます。
カラム位置は整数であり、1 から始まります。


041 group_concat関数の長さ上限

group_concat関数の結果の長さには上限があって、
そのデフォルト値は1024バイト

SET SESSION group_concat_max_len = 9876;
で変更することができる。


042 現在日時の表示

select Date_Format(Now(),'%Y/%m/%d %H:%i:%S') as tes;

+---------------------+
| tes                 |
+---------------------+
| 2020/01/01 23:59:59 |
+---------------------+


043 OrderByでのNullの判定

MySQLのOrderByはNULLを最小とみなす。

select Val
  from (select 1 as Val union
        select 2 union
        select 3 union
        select null) a
order by Val;

+------+
| Val  |
+------+
| NULL |
|    1 |
|    2 |
|    3 |
+------+


044 文字列の指定文字列以降を抽出

5.7までは、InStr関数とSubStr関数を組み合わせる必要あり

select SubStr(WkStr,InStr(WkStr,'5')) as tes
  from (select '123456789' as WkStr) tmp;

+-------+
| tes   |
+-------+
| 56789 |
+-------+

MySQL8.0からは、正規表現が使える
select RegExp_SubStr(WkStr,'5.*$') as tes
  from (select '123456789' as WkStr) tmp;


045 InStr関数とFind_IN_Set関数とChar_Length関数

InStr関数とFind_IN_Set関数は、
ヒットしたら、その位置(1以上)を返す
ヒットしなかったら、0を返す。
0か1以上を返すので、そのまま、論理演算に使うこともできる。

Length関数とChar_Length関数も、文字列の空判定として、そのまま、論理演算に使える。
select Val,Char_Length(Val) as Len
  from (select null as Val union all
        select '' union all
        select 'ABC') t;

+------+------+
| Val  | Len  |
+------+------+
| NULL | NULL |
|      |    0 |
| ABC  |    3 |
+------+------+


046 オートインクリメントと主キーの設定

create Temporary table Test1(
ColA int Auto_Increment Primary Key,
ColB int);
desc Test1;

create Temporary table Test2(
ColA int Auto_Increment,
ColB int,
Primary Key(ColA));
desc Test2;


047 全部のテーブルのdrop文を作成

select concat('drop table ' , TABLE_NAME , ';') as ddl
  from Information_Schema.Tables
 where TABLE_SCHEMA = 'test';


048 MySQL徹底入門(4版)の読書メモ

232ページ
MySQLのデフォルトのトランザクション分離レベルは、
RepeatableReadで、
同じトランザクションでは、一貫性のあるデータ読取ができる。

250ページ
MySQLに接続し、statusコマンドを使うと
基本的な情報を表示できる。

269ページ
helpコマンドは、関数の指定もできる。

help find_in_set
help instr
help left

513ページ
MySQL 5.7.6から
Oracleの仮想列のような機能である
generated columnという機能が使える。