MySQLメモ

README.md

docker-compose.yml

** --secure-file-priv='/tmp'** を付けないとcsvを吐き出せない

version: '3.4'

services:
  mysql-test:
    container_name: mysql-test
    image: mysql:8.0.17
    environment:
      TERM: linux
      LANG: "C.UTF-8"
      LOG_ROTATION_DAY: 7
      MYSQL_ROOT_PASSWORD: Passw0rd
      MYSQL_DATABASE: test
    logging:
      driver: json-file
      options:
        max-size: "50m"
        max-file: "2"
    restart: "no"
    ports:
      - 3306:3306
    volumes:
      - mysql-test-database-data:/var/lib/mysql:cached
    command: --innodb-use-native-aio=0 --secure-file-priv='/tmp'

volumes:
  mysql-test-database-data:
    external:
      name: mysql-test-database-data

test.csv

test.php

<?php
declare(strict_types=1);

define('TABLE_NAME', 2);
define('COLUMN_NAME', 3);
define('COLUMN_DEFAULT', 5);
define('IS_NULLABLE', 6);
define('DATA_TYPE', 7);
define('NUMERIC_PRECISION', 8);
define('COLUMN_COMMENT', 19);

$fp = fopen('test.csv', 'r');
if ($fp === false) {
  echo "Could not open file\n";
  exit -1;
}

while ($buf = fgetcsv($fp)) {
  echo "{$buf[COLUMN_COMMENT]}({$buf[COLUMN_NAME]})\n";
}
fclose($fp);

test.sql

select * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tableName';
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+-------------+------------+-------+---------------------------------+---------------------+-----------------------+--------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME  | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES                      | COLUMN_COMMENT      | GENERATION_EXPRESSION | SRS_ID |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+-------------+------------+-------+---------------------------------+---------------------+-----------------------+--------+
| def           | test         | tableName  | columnName1 |                1 | NULL           | YES         | int       |                     NULL |                   NULL |                10 |             0 |               NULL | NULL               | NULL            | int(11)     |            |       | select,insert,update,references | 1つ目のカラム       |                       |   NULL |
| def           | test         | tableName  | columnName2 |                2 | NULL           | YES         | text      |                    65535 |                  65535 |              NULL |          NULL |               NULL | utf8               | utf8_general_ci | text        |            |       | select,insert,update,references | 2つ目のカラム       |                       |   NULL |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+-------------+------------+-------+---------------------------------+---------------------+-----------------------+--------+
2 rows in set (0.00 sec)


// csvにdump
select * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tableName' 
    INTO OUTFILE '/tmp/tableName.csv'
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY '"';

$ php test.php 
1つ目のカラム(columnName1)
2つ目のカラム(columnName2)