mysql 各種使い方

2021 Oct. 24.

2019 Oct. 21.
2019 Jan. 03.
2018 Dec. 31.
2018 Oct. 21.
2018 Aug. 26.
2017 Nov. 23.
2017 Apr. 05.

テキストファイルからの入力

    LOAD DATA INFILE "ファイル名" INTO TABLE テーブル名 FIELDS TERMINATED BY ',(区切り文字)' ENCLOSED BY '"' ( 引用符で囲まれるカラム, .. ) ;

      入力ファイルにおいて数値は二重引用符に囲まれていなくてよい。
      strict-modeに STRICT_TRANS_TABLES または STRICT_ALL_TABLES がセットされていると
      入出力のカラム数が一致しない場合エラーとなる。

    mysql> load data infile "20150822diary.csv" into table diary fields terminated by ',' enclosed by '"' ( date, note, sunrise, sunset, weather, highest_temparature, lowest_temparature, weight, money, people_met, place_visited, about_family, time_awoke, time_sleep_yesterday, note2 ) ;

    mysql> load data infile "20150906postgresql-meibo.csv" into table people fields terminated by ',' enclosed by '"' ( name,kana,kankei1, kankei2, keisyo,heiki_name,heiki_keisyo,mail_adr,job,renrakusaki,bikou,phone_keitai,genjusyo_postal,genjusyo1,genjusyo2,genjusyo_phone,nenga_postal,nengajusyo1,nengajusyo2,nenga_phone ) ;

　　postgresqlからcsvファイル経由でデータコンバートする場合は全データを二重引用符で囲み、booleanデータは0/1(整数)に変換しておく

    mysql> load data infile "20150909people.csv" into table people fields terminated by ',' enclosed by '"' ( name,kana,kankei1, kankei2, keisyo,heiki_name,heiki_keisyo,mail_adr,job,renrakusaki,bikou,phone_keitai,genjusyo_postal,genjusyo1,genjusyo2,genjusyo_phone,nenga_postal,nengajusyo1,nengajusyo2,nenga_phone,isneednenga,issendnenga2000,isgetnenga2000,issendnenga2001, isgetnenga2001,issendnenga2002, isgetnenga2002,issendnenga2003, isgetnenga2003,issendnenga2004, isgetnenga2004,issendnenga2005, isgetnenga2005,issendnenga2006, isgetnenga2006,issendnenga2007, isgetnenga2007,issendnenga2008, isgetnenga2008,issendnenga2009, isgetnenga2009,issendnenga2010, isgetnenga2010,issendnenga2011, isgetnenga2011,ismochu2012,issendnenga2012, isgetnenga2012,ismochu2013,issendnenga2013, isgetnenga2013,ismochu2014,issendnenga2014,isgetnenga2014,ismochu2015,issendnenga2015, isgetnenga2015 ) ;

csvファイルからの読み込み

        LOAD DATA INFILE "ファイル名" INTO TABLE テーブル名 FIELDS TERMINATED BY

 ',(区切り文字)' ENCLOSED BY '"' ( 引用符で囲まれるカラム, .. ) ;

      入力ファイルにおいて数値は二重引用符に囲まれていなくてよい。
      strict-modeに STRICT_TRANS_TABLES または STRICT_ALL_TABLES がセットされていると入出力のカラム数が一致しない場合エラーとなる。

    mysql> load data infile "20150822diary.csv" into table diary fields terminated by ',' enclosed by '"' ( date, note, sunrise, sunset, weather, highest_temparature, lowest_temparature, weight, money, people_met, place_visited, about_family, time_awoke, time_sleep_yesterday, note2 ) ;

    mysql> load data infile "20150906postgresql-meibo.csv" into table people fields terminated by ',' enclosed by '"' ( name,kana,kankei1, kankei2, keisyo,heiki_name,heiki_keisyo,mail_adr,job,renrakusaki,bikou,phone_keitai,genjusyo_postal,genjusyo1,genjusyo2,genjusyo_phone,nenga_postal,nengajusyo1,nengajusyo2,nenga_phone ) ;

　postgresqlからcsvファイル経由でデータコンバートする場合は全データを二重引用符で囲み、booleanデータは0/1(整数)に変換しておく
    mysql> load data infile "20150909people.csv" into table people fields terminated by ',' enclosed by '"' ( name,kana,kankei1, kankei2, keisyo,heiki_name,heiki_keisyo,mail_adr,job,renrakusaki,bikou,phone_keitai,genjusyo_postal,genjusyo1,genjusyo2,genjusyo_phone,nenga_postal,nengajusyo1,nengajusyo2,nenga_phone,isneednenga,issendnenga2000,isgetnenga2000,issendnenga2001, isgetnenga2001,issendnenga2002, isgetnenga2002,issendnenga2003, isgetnenga2003,issendnenga2004, isgetnenga2004,issendnenga2005,  isgetnenga2005,issendnenga2006, isgetnenga2006,issendnenga2007, isgetnenga2007,issendnenga2008, isgetnenga2008,issendnenga2009, isgetnenga2009,issendnenga2010, isgetnenga2010,issendnenga2011, isgetnenga2011,ismochu2012, issendnenga2012, isgetnenga2012,ismochu2013,issendnenga2013, isgetnenga2013,ism、ochu2014,issendnenga2014,isgetnenga2014,ismochu2015, issendnenga2015, isgetnenga2015 ) ;

テキストファイルに出力する。

    ユーザにfile権限を付与する。
        $ mysql -p -u root DBNAME

        mysql> grant file on *.* to USER@localhost ;

        mysql> exit

    $ mysql -p -u USERNAME DBNAME

    (カンマ区切り、二重引用符囲みで/var/lib/mysql-files/ディレクトリに出力)
        --secure-file-privが設定された状態であれば
            SELECT @@secure_file_priv; を実行し
            出力表示されたディレクトリにCSVファイルを保存するようにする。

    mysql> select * from TABLE WHERE CONDITION INTO OUTFILE "/var/lib/mysql-files/TEXTFILENAME" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\n";

    (列名を先頭行につけて出力)
    mysql> select 'col1','col2', ... union select * from TABLE WHERE CONDITION INTO OUTFILE "/var/lib/mysql-files/TEXTFILENAME" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\n";
   
        書き込み先はmysqlユーザが書き込み権限のあるディレクトリでないといけない。
        /tmpディレクトリが無難。
        書込先ファイルの上書きはできないので、同名ファイルは削除しておく。

Output csv-file for standard-output.(二重引用符囲み無し。カンマ区切り)
  $ mysql -u USER -p -e "SELECT *  from TABLE" DATABASE | sed -e 's/\t/,/g' > outfile.csv

    (sample)
    $ mysql --user epgrec -p -e 'SELECT path,starttime,title,description,type,channel from Recorder_reserveTbl where complete = 1 and dirty = 0 order by starttime desc' epgrec | sed -e 's/\t/,/g' > ~/video_list.csv

sql結果のファイル出力

  $ mysql -p -u epgrec epgrec > OUTFILE
  SQL文の入力待ちになったところで
  select path from Recorder_reserveTbl order by path asc ;
  \q
  と入力するとファイルが作成される。

データバックアップ リストア

    全データ

        バックアップ
            $ mysqldump -u root -p -x --all-databases > FILE
     
        リストア
            $ mysql -u root -p < FILE

    データベース単位

        バックアップ
            $ mysqldump -u USER -p DBNAME > FILE

        リストア
　　　データベース作成
　　　　mysqlコマンド起動後、インタラクティブに
　　　　$ mysql -u root -p
　　　　mysql> create database DBNAME CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ;

　　　　mysql> grant all on DBNAME.* to 'USERNAME'@'localhost' identified by 'PASSWORD' ;

　　　　mysql> grant all on DBNAME.* to 'USERNAME'@'127.0.0.1' identified by 'PASSWORD' ;

　　　　mysql> flush privileges;

　　　　mysql> exit

　　　リストア
　　　　$ mysql -u USER -p DBNAME < FILE

    テーブル単位(複数テーブルを指定可能)

        mysqldump -u USER -p DB TABLE1 TABLE2 > FILE

    /var/lib/mysqのコピーによるバックアップ
        # service mysql stop
        # rsync -av /var/lib/mysql BACKUP-DIR/
        # service mysql restart

 /var/lib/mysqのコピーによるバックアップ

        # service mysql stop
        # rsync -av /var/lib/mysql BACKUP-DIR/
        # service mysql restart

 

別のテーブルの値でデータ更新

テーブルmeiboのbikouの値を、id値が一致するpeopleテーブルのbikouにセットする例

  update people, meibo set people.bikou = meibo.bikou where people.id = meibo.id ;

MySQL 5.0の文字コード設定

 

    以下の内容で、/etc/mysql/conf.d/に character_set.cnfというファイル

    [client]
    default-character-set = utf8

    [mysqld]
    default-character-set = utf8
    "skip-character-set-client-handshake"は無い方がよいらしい

    [mysqldump]
    default-character-set = utf8

    [mysql]
    default-character-set = utf8

my.cnf / my.iniの設定

  skip-networking
    設定されている場合オフにする。

  port
    デフォルト値は3306

/etc/hosts.allow

  許可  mysqld: ALL
        mysqld: 192.168.1.
        mysqld: 127.0.0.1

外部ホストに対しmysqlのポート3306を開く

  # ufw enable
  # ufw allow 3306/tcp
  # ufw allow 3306/udp

  そのポートでLISTENとなっていることの確認
    $ netstat -na | grep 3306
       
    $ telnet -h xxx.xxx.xxx.xxx -P 3306

 TCP/IPソケット有効確認

      $ mysql -h 127.0.0.1 -P 3306 -u root -p

mysql 外部ホストからのアクセス許可/不許可

外部からの接続を制限するには、GRANTコマンドでユーザ毎に指定する。

  /etc/mysql/my.confの "bind-address = xxx.xxx.xxx.xxx"
　　外部からのアクセスを許可するには、"bind-address = 0.0.0.0"の１行だけを記載する。
　　localhostだけのアクセスを許可するには、"bind-address = 127.0.0.1"の１行だけを記載する。

    　"bind-address = xxx.xxx.xxx.xxx" は、
　　  「サーバが複数の IPアドレスを持っている場合に、特定のアドレスでしか
  　　待ち受けないようにする」オプション。
  　　複数行書かれていると、最後の行のみが有効。
  　　１行に、スペース区切りで複数アドレスが書かれていると、最初のアドレスが有効。
  　　bind-address行記載が無ければ、サーバーが持っている全てのIPアドレスで待ち受ける。（localhostと192.168.xxx.xxxとか）
  　　"bind-address = 0.0.0.0" でも、サーバーが持っている全てのIPアドレスで待ち受ける。

GRANTコマンドによるユーザ登録と外部アクセス制御

    mysql> grant all privileges on DBNAME.* to USER@"xxx.xxx.xxx.xxx" identified by 'パスワード' with grant option;
    mysql> flush privileges;

    mysql> grant all privileges on DBNAME.* to USER@"HOST" identified by 'パスワード' with grant option;
    mysql> flush privileges;

    すべてのホストから接続できるようにする
      mysql> grant all privileges on DBNAME.* to USER@"%" identified by 'パスワード' with grant option;
      mysql> flush privileges;

    192.168.1.0/24からの接続を許可する。
      mysql> grant all privileges on DBNAME.* to USER@"192.168.1.%" identified by 'パスワード' with grant option;
      mysql> flush privileges;
   
    example.comからの接続を許可する。
      mysql> grant all privileges on DBNAME.* to USER@"example.com" identified by 'パスワード' with grant option;
      mysql> flush privileges;
     
    全データベースへのアクセスを許可する。
      mysql> grant all privileges on *.* to USER@"example.com" identified by 'パスワード' with grant option;
      mysql> flush privileges;

Strict mode(カラムに不正な値を入れた場合に警告ではなくエラーを返す)の

有効化(ENUM型でリスト外の値をエラーにする)

    debian/ubuntu
        /etc/mysql/my.cnfの[mysqld]セクション内に
        sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
        の1行を記載する。
 

mysqlのユーザー一覧

    $ mysql -p -u root mysql
    mysql>  select user,host from user;

 文字セットと照合順序を指定したデータベース作成

  mysql> CREATE DATABASE DBNAME DEFAULT CHARACTER SET utf8 collate utf8_unicode_ci ;

    collate(照合順序)とは、文字を比較(一致/不一致や表示順)する際のルール。
    mysqlのデフォルトcollateであるutf8_general_ciでは、大文字-小文字を同一視するが、
    utf8_unicode_ciでは、さらに半角-全角も同一視する。

データベースのデフォルト文字コードの設定

    ALTER DATABASE dbname default character set=utf8;

データベース利用開始、データベース選択、テーブル確認

  $ mysql -p -u USER DATABASE
  mysql> SHOW DATABASES;
  mysql> use DATABASENAME;
  mysql> SHOW TABLES;
  mysql> show full columns from TABLE ;

 テーブルの構造を表示

show columns from TABLENAME;

 

tableの全カラム名取得

    mysql> SHOW COLUMNS FROM TABLE;

    $ mysql -u user -p -Ns -e "DESC table" database | cut -f 1
      -N 結果のヘッダーを非表示にする
      -s mysql特有の枠を非表示にする
 

Epgrecの Recorder_reserveTblテーブルのprogram_idに関し、値が連続していない行（program_id + 1 のprogram_id値が存在しない行）をselectする

    select program_id from Recorder_reserveTbl R1 where ( select count(*) from Recorder_reserveTbl R2 where R2.program_id = R1.program_id + 1 ) = 0 order by program_id;

カラムに連続番号が自動で入力される設定

  １，データ型は「int」型などの整数型を設定する
  ２，「auto_increment」を付ける
  ３，一意に設定する
      「primary key」に
     
  例）create table renban(a int auto_increment primary key,b varchar(10));
      insert into renban (b) values ('あ');

  自動連続カラムには自動で値が入るので、データ入力時にはこのカラムを無視してよい。



テーブル作成例
mysql> CREATE TABLE diary ( id INTEGER UNSIGNED not null auto_increment primary key, date DATE NOT NULL, note text, sunrise TIME,   sunset TIME,   weather text, highest_temparature INTEGER,   lowest_temparature INTEGER, weight FLOAT UNSIGNED,   money integer,   people_met text,   place_visited text,   about_family text,   time_awoke time, time_sleep_yesterday time, note2 text, index(id));

mysql> CREATE TABLE people ( id INTEGER UNSIGNED not null auto_increment primary key, name text, kana text, kankei1 text, kankei2 text, keisyo text, heiki_name text, heiki_keisyo text, mail_adr text, job text, renrakusaki text,bikou text,phone_keitai text,genjusyo_postal VARCHAR(14), genjusyo1 VARCHAR(80),genjusyo2 VARCHAR(72),genjusyo_phone text,nenga_postal VARCHAR(14),nengajusyo1 VARCHAR(80),nengajusyo2 VARCHAR(72),nenga_phone text,isneednenga boolean DEFAULT false CHECK(isneednenga > -1 AND isneednenga < 2 ), issendnenga2000 boolean DEFAULT false CHECK(issendnenga2000 > -1 AND issendnenga2000 < 2 ), isgetnenga2000 boolean DEFAULT false CHECK(isgetnenga2000 > -1 AND isgetnenga2000 < 2 ),issendnenga2001 boolean DEFAULT false CHECK(issendnenga2001 > -1 AND issendnenga2001 < 2 ), isgetnenga2001 boolean DEFAULT false CHECK(isgetnenga2001 > -1 AND isgetnenga2001 < 2 ),issendnenga2002 boolean DEFAULT false CHECK(issendnenga2002 > -1 AND issendnenga2002 < 2 ), isgetnenga2002 boolean DEFAULT false CHECK(isgetnenga2002 > -1 AND isgetnenga2002 < 2 ),issendnenga2003 boolean DEFAULT false CHECK(issendnenga2003 > -1 AND issendnenga2003 < 2 ), isgetnenga2003 boolean DEFAULT false CHECK(isgetnenga2003 > -1 AND isgetnenga2003 < 2 ),issendnenga2004 boolean DEFAULT false CHECK(issendnenga2004 > -1 AND issendnenga2004 < 2 ), isgetnenga2004 boolean DEFAULT false CHECK(isgetnenga2004 > -1 AND isgetnenga2004 < 2 ),issendnenga2005 boolean DEFAULT false CHECK(issendnenga2005 > -1 AND issendnenga2005 < 2 ), isgetnenga2005 boolean DEFAULT false CHECK(isgetnenga2005 > -1 AND isgetnenga2005 < 2 ),issendnenga2006 boolean DEFAULT false CHECK(issendnenga2006 > -1 AND issendnenga2006 < 2 ), isgetnenga2006 boolean DEFAULT false CHECK(isgetnenga2006 > -1 AND isgetnenga2006 < 2 ),issendnenga2007 boolean DEFAULT false CHECK(issendnenga2007 > -1 AND issendnenga2007 < 2 ), isgetnenga2007 boolean DEFAULT false CHECK(isgetnenga2007 > -1 AND isgetnenga2007 < 2 ),issendnenga2008 boolean DEFAULT false CHECK(issendnenga2008 > -1 AND issendnenga2008 < 2 ), isgetnenga2008 boolean DEFAULT false CHECK(isgetnenga2008 > -1 AND isgetnenga2008 < 2 ),issendnenga2009 boolean DEFAULT false CHECK(issendnenga2009 > -1 AND issendnenga2009 < 2 ), isgetnenga2009 boolean DEFAULT false CHECK(isgetnenga2009 > -1 AND isgetnenga2009 < 2 ),issendnenga2010 boolean DEFAULT false CHECK(issendnenga2010 > -1 AND issendnenga2010 < 2 ), isgetnenga2010 boolean DEFAULT false CHECK(isgetnenga2010 > -1 AND isgetnenga2010 < 2 ),issendnenga2011 boolean DEFAULT false CHECK(issendnenga2011 > -1 AND issendnenga2011 < 2 ), isgetnenga2011 boolean DEFAULT false CHECK(isgetnenga2011 > -1 AND isgetnenga2011 < 2 ),ismochu2012 boolean DEFAULT false CHECK(ismochu2012 > -1 AND ismochu2012 < 2 ),issendnenga2012 boolean DEFAULT false CHECK(issendnenga2012 > -1 AND issendnenga2012 < 2 ), isgetnenga2012 boolean DEFAULT false CHECK(isgetnenga2012 > -1 AND isgetnenga2012 < 2 ),ismochu2013 boolean DEFAULT false CHECK(ismochu2013 > -1 AND ismochu2013 < 2 ),issendnenga2013 boolean DEFAULT false CHECK(issendnenga2013 > -1 AND issendnenga2013 < 2 ), isgetnenga2013 boolean DEFAULT false CHECK(isgetnenga2013 > -1 AND isgetnenga2013 < 2 ),ismochu2014 boolean DEFAULT false CHECK(ismochu2014 > -1 AND ismochu2014 < 2 ),issendnenga2014 boolean DEFAULT false CHECK(issendnenga2014 > -1 AND issendnenga2014 < 2 ), isgetnenga2014 boolean DEFAULT false CHECK(isgetnenga2014 > -1 AND isgetnenga2014 < 2 ),ismochu2015 boolean DEFAULT false CHECK(ismochu2015 > -1 AND ismochu2015 < 2 ),issendnenga2015 boolean DEFAULT false CHECK(issendnenga2015 > -1 AND issendnenga2015 < 2 ), isgetnenga2015 boolean DEFAULT false CHECK(isgetnenga2015 > -1 AND isgetnenga2015 < 2 ),ismochu2016 boolean DEFAULT false CHECK(ismochu2016 > -1 AND ismochu2016 < 2 ),issendnenga2016 boolean DEFAULT false CHECK(issendnenga2016 > -1 AND issendnenga2016 < 2 ), isgetnenga2016 boolean DEFAULT false CHECK(isgetnenga2016 > -1 AND isgetnenga2016 < 2 ),index(id));

mysql> CREATE TABLE people ( id INTEGER UNSIGNED not null auto_increment primary key, name text, kana text, kankei1 text, kankei2 text, keisyo text, heiki_name text, heiki_keisyo text, mail_adr text, job text, renrakusaki text,bikou text,phone_keitai text,genjusyo_postal VARCHAR(14), genjusyo1 VARCHAR(80),genjusyo2 VARCHAR(72),genjusyo_phone text,nenga_postal VARCHAR(14),nengajusyo1 VARCHAR(80),nengajusyo2 VARCHAR(72),nenga_phone text,isneednenga boolean  , issendnenga2000 boolean  , isgetnenga2000 boolean  ,issendnenga2001 boolean  , isgetnenga2001 boolean  ,issendnenga2002 boolean  , isgetnenga2002 boolean  ,issendnenga2003 boolean  , isgetnenga2003 boolean  ,issendnenga2004 boolean  , isgetnenga2004 boolean  ,issendnenga2005 boolean  , isgetnenga2005 boolean  ,issendnenga2006 boolean  , isgetnenga2006 boolean  ,issendnenga2007 boolean  , isgetnenga2007 boolean  ,issendnenga2008 boolean  , isgetnenga2008 boolean  ,issendnenga2009 boolean  , isgetnenga2009 boolean  ,issendnenga2010 boolean  , isgetnenga2010 boolean  ,issendnenga2011 boolean  , isgetnenga2011 boolean  ,ismochu2012 boolean  ,issendnenga2012 boolean  , isgetnenga2012 boolean  ,ismochu2013 boolean  ,issendnenga2013 boolean  , isgetnenga2013 boolean  ,ismochu2014 boolean  ,issendnenga2014 boolean  , isgetnenga2014 boolean  ,ismochu2015 boolean  ,issendnenga2015 boolean  , isgetnenga2015 boolean  ,ismochu2016 boolean  ,issendnenga2016 boolean  , isgetnenga2016 boolean  ,index(id)) ;