カテゴリー: Database

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)

SELECT ROW_COUNT();

[ 利用例 ]

更新結果をログテーブルに追加する

insert into test (
field1, field2
)
select * from test2;

insert into log_updt (NM_TBL, CNT_AFFECT) VALUES (
'test', (select ROW_COUNT())
); 

データベースの変更 : use [ データベース名 ]

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

データベース一覧 : show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

テーブル一覧 : show tables;

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

テーブルスキーマ (1) : show create table [ テーブル名 ]

mysql> show create table time_zone;
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                         |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| time_zone | CREATE TABLE `time_zone` (
  `Time_zone_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Use_leap_seconds` enum('Y','N') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Time_zone_id`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Time zones' |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

テーブルスキーマ (2) : desc [ テーブル名 ]

mysql> desc m_todou;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| CD     | tinyint(4)  | NO   | PRI | NULL    |       |
| K_NAME | varchar(8)  | NO   |     | NULL    |       |
| H_NAME | varchar(12) | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

テーブルスキーマ (3) : show full columns from [ テーブル名 ]

mysql> show full columns from m_todou;
+--------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field  | Type        | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+--------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| CD     | tinyint(4)  | NULL            | NO   | PRI | NULL    |       | select,insert,update,references |         |
| K_NAME | varchar(8)  | utf8_general_ci | NO   |     | NULL    |       | select,insert,update,references |         |
| H_NAME | varchar(12) | utf8_general_ci | NO   |     | NULL    |       | select,insert,update,references |         |
+--------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
3 rows in set (0.00 sec)

ストアドスキーマ : show create procedure [ ストアドプロシージャー名 ]

mysql> show create procedure test \G;
*************************** 1. row ***************************
           Procedure: test
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(
        IN `tstvalue` INT
)
    COMMENT 'nothing to reffer'
BEGIN
SELECT tstvalue;
END
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

ERROR:
No query specified

ファンクションスキーマ : show create function [ ファンクション名 ]

mysql> show create function fc_csv_idxtxt \G;
*************************** 1. row ***************************
            Function: fc_csv_idxtxt
            sql_mode: NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `fc_csv_idxtxt`(
        `src` VARCHAR(9000),
        `idx` smallint
) RETURNS varchar(9000) CHARSET utf8
    COMMENT 'CSV列指定位置取得'
begin

declare rtntxtcsv varchar(9000) default '';

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(src, ',', idx), ',', -1) INTO rtntxtcsv;

return rtntxtcsv;

end
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

ユーザー一覧 : select host,user,plugin from mysql.user;

mysql> select host,user,plugin from mysql.user limit 0, 3;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
+-----------+------------------+-----------------------+
3 rows in set (0.00 sec)

終了 : exit or quit or \q

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 592
Server version: 8.0.13 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 593
Server version: 8.0.13 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> quit
Bye
[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 596
Server version: 8.0.13 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \q
Bye
[root@localhost ~]# 

(SELECT @num := -1) as dmy で連番用の仮想テーブルを記述、
@num := @num + 1 AS ID のような列を記述します。

mysql> SELECT
    ->   @num := @num + 1 AS ID,
    ->   CD,
    ->   K_NAME, H_NAME
    -> FROM
    ->   (SELECT @num := -1) as dmy,
    ->   m_todou
    -> LIMIT 0, 5;
+------+----+-----------+--------------------+
| ID   | CD | K_NAME    | H_NAME             |
+------+----+-----------+--------------------+
|    0 |  1 | 北海道    | ほっかいどう       |
|    1 |  2 | 青森県    | あおもりけん       |
|    2 |  3 | 岩手県    | いわてけん         |
|    3 |  4 | 宮城県    | みやぎけん         |
|    4 |  5 | 秋田県    | あきたけん         |
+------+----+-----------+--------------------+
5 rows in set (0.00 sec)

mysql> show create table m_todou;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                    |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| m_todou | CREATE TABLE `m_todou` (
  `CD` tinyint(4) NOT NULL,
  `K_NAME` varchar(8) NOT NULL,
  `H_NAME` varchar(12) NOT NULL,
  PRIMARY KEY (`CD`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='都道府県マスタ'        |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

SQL 第2版 ゼロからはじめるデータベース操作【電子書籍】[ ミック ] 価格：2068円 (2022/12/27時点) 楽天で購入

【中古】実践ハイパフォ-マンスMySQL 第3版/オライリ-・ジャパン/バロン・シュワルツ（大型本） 価格：4770円（税込、送料無料) (2023/1/29時点) 楽天で購入

まだ小規模、少数の運用しかしてませんので、書くことありません。