· 8 years ago · Aug 21, 2018, 04:22 AM
1# mysql --version
2mysql Ver 15.1 Distrib 10.1.35-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
3
4# mysql -uroot -p
5MariaDB [(none)]> create schema `test_ai`;
6Query OK, 1 row affected (0.00 sec)
7
8MariaDB [(none)]> use `test_ai`;
9Database changed
10
11MariaDB [test_ai]> DROP TABLE IF EXISTS `my_table`;
12Query OK, 0 rows affected, 1 warning (0.00 sec)
13
14MariaDB [test_ai]> CREATE TABLE `my_table` (`id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(64) NOT NULL, `created_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
15Query OK, 0 rows affected (0.02 sec)
16
17MariaDB [test_ai]> INSERT INTO `my_table` (`name`, `created_at`) VALUES ('data 1', '0000-00-00 00:00:00');
18MariaDB [test_ai]> INSERT INTO `my_table` (`name`, `created_at`) VALUES ('data 2', '0000-00-00 00:00:00');
19MariaDB [test_ai]> INSERT INTO `my_table` (`name`, `created_at`) VALUES ('data 3', '0000-00-00 00:00:00');
20MariaDB [test_ai]> INSERT INTO `my_table` (`name`, `created_at`) VALUES ('data 4', '0000-00-00 00:00:00');
21MariaDB [test_ai]> INSERT INTO `my_table` (`name`, `created_at`) VALUES ('data 5', '0000-00-00 00:00:00');
22MariaDB [test_ai]> INSERT INTO `my_table` (`name`, `created_at`) VALUES ('data 6', '0000-00-00 00:00:00');
23
24
25MariaDB [test_ai]> INSERT INTO `my_table` (`name`, `created_at`) VALUES ('data 1', '0000-00-00 00:00:00');
26Query OK, 1 row affected (0.00 sec)
27
28MariaDB [test_ai]> SHOW TABLE STATUS FROM `test_ai` WHERE `name` LIKE '%my_table%' ;
29+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
30| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
31+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
32| my_table | InnoDB | 10 | Compact | 6 | 2730 | 16384 | 0 | 0 | 0 | 7 | 2018-08-21 10:27:03 | NULL | NULL | utf8mb4_general_ci | NULL | | |
33+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
341 row in set (0.01 sec)
35
36MariaDB [test_ai]> SELECT MAX(id) from my_table;
37+---------+
38| MAX(id) |
39+---------+
40| 6 |
41+---------+
421 row in set (0.01 sec)
43
44MariaDB [test_ai]> DELETE FROM my_table WHERE id IN (1,2,3,4,5,6);
45Query OK, 6 rows affected (0.01 sec)
46
47MariaDB [test_ai]> SHOW TABLE STATUS FROM `test_ai` WHERE `name` LIKE '%my_table%' ;
48+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
49| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
50+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
51| my_table | InnoDB | 10 | Compact | 6 | 2730 | 16384 | 0 | 0 | 0 | 7 | 2018-08-21 10:27:03 | NULL | NULL | utf8mb4_general_ci | NULL | | |
52+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
531 row in set (0.01 sec)
54
55MariaDB [test_ai]> exit
56Bye
57
58--------------
59
60# sudo systemctl restart mariadb
61# mysql -uroot -p
62MariaDB [(none)]> SHOW TABLE STATUS FROM `test_ai` WHERE `name` LIKE '%my_table%' ;
63+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
64| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
65+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
66| my_table | InnoDB | 10 | Compact | 1 | 16384 | 16384 | 0 | 0 | 0 | 1 | 2018-08-21 11:08:44 | NULL | NULL | utf8mb4_general_ci | NULL | | |
67+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
681 row in set (0.00 sec)