Xin Xing's talk about metadata in mysql

Xin Xing's talk about metadata in mysql

First, let's explain what metadata is. The so-called metadata is the data that represents the data. The data is varied. In short, as long as it is not the data we store in the database, most of them can be understood as metadata. So how can we get the metadata?

In general, there are three ideas: First, various show, second, various select, and third, which are commands in the mysql command line, not SQL statements.

First, let's take a look at the first one. Here I will list the usage of show statements that you are familiar with. In fact, we often use show to view information, for example:

Show databases; show tales; show create table name; show index from table name;... and so on

The second method is to extract the relevant information from the mysql database or the information_schema database through select. For example, you can view the related information of data fragments from the free_data field in the tables Table, I have already introduced this in my previous blog post, so we will not talk nonsense here.

The third is to use some commands provided by mysql to view the user name and password. below is the code for viewing the corresponding metadata, hoping to help you:

Microsoft Windows [version 6.1.7601] Copyright (c) 2009 Microsoft Corporation. All rights reserved. C: \ Users \ Administrator> mysqlshow-u root-prootmysqlshow: [Warning] Using a password on the command line interface can be insecure. + -------------------- + | Databases | + -------------------- + | information_schema | mysql | performance_schema | test | xinxing | + -------------------- + C: \ Users \ Administrator> mysqlshow-u root-proot xinxingmysqlshow: [Warning] Using a password on the command line interface can be insecure. database: xinxing + -------- + | Tables | + -------- + | xin | + -------- + C: \ Users \ Administrator> mysqlshow-u root-proot -- status xinxingmysqlshow: [Warning] Using a password on the command line interface can be insecure. database: xinxing + ------ + -------- + --------- + ------------ + ------ + upper + ------------- + upper + -------------- + ----------- + upper + lower + upper + ---------- + ---------------- + ----------- + | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | bytes | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | + ------ + -------- --------- + ------------ + ------ + ---------------- + ------------- + hour + -------------- + ----------- + hour + ---------- + hour + ----------- + | xin | MyISAM | 10 | Fixed | 3 | 121 | 363 | 34058472181989375 | 1024 | 0 | 13:19:02 | 13:41:25 | 13:41:25 | utf8_general_ci | + ------ + -------- + --------- + ---------- + -------- + ---------------- + ------------- + ----------------- + -------------- + ----------- + ---------------- + accept + ----------------- + ---------- + ---------------- + ----------- + C: \ Users \ Administrator>

The following are some operations that I use mysqldump to view the table structure and hope to be useful to you:

C:\Users\Administrator>mysqldump -u root -proot --no-data xinxing xinmysqldump: [Warning] Using a password on the command line interface can be insecure.-- MySQL dump 10.13  Distrib 5.7.3-m13, for Win64 (x86_64)---- Host: localhost    Database: xinxing-- -------------------------------------------------------- Server version       5.7.3-m13/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;---- Table structure for table `xin`--DROP TABLE IF EXISTS `xin`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `xin` (  `c` char(40) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2014-08-25 20:49:08

Xin Xing, looking forward to your attention ..