首先回答,这个语句有,但是不是8.0才出现,5.6就已经有了8.4.2.4 Using PROCEDURE ANALYSEdev.mysql.com
PROCEDURE ANALYSE 通过分析select查询结果对现有的表的每一列给出优化的建议,
语法
SELECT...FROM...WHERE...PROCEDURE ANALYSE([max_elements,[max_memory]])
怎么使用,如下:
mysql> desc TMS_CARRIER_INF;
+---------------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+-------------------+----------------+
| ID | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| SYS_INF | varchar(255) | YES | | NULL | |
| TYPE | tinyint(1) | NO | | 0 | |
| CLIENT_ID | varchar(255) | YES | | NULL | |
| CLIENT_SECRET | varchar(255) | YES | | NULL | |
| GRANT_TYPE | varchar(255) | YES | | NULL | |
| SERVICE_URL | varchar(255) | YES | | NULL | |
| CARRIER_ATTR | varchar(255) | YES | | NULL | |
| CUST_CODE | varchar(255) | YES | | NULL | |
| COUNTRY_CODE | varchar(4) | YES | | NULL | |
| CREATE_TIME | datetime | NO | | CURRENT_TIMESTAMP | |
| UPDATE_TIME | datetime | NO | | CURRENT_TIMESTAMP | |
+---------------+---------------------+------+-----+-------------------+----------------+
12 rows in set (0.02 sec)
mysql> SELECT SYS_INF,GRANT_TYPE FROM TMS_CARRIER_INF PROCEDURE ANALYSE(10, 2000)\G;
*************************** 1. row ***************************
Field_name: hc_tms.TMS_CARRIER_INF.SYS_INF
Min_value: NINJAVAN
Max_value: NINJAVAN
Min_length: 8
Max_length: 8
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 8.0000
Std: NULL
Optimal_fieldtype: ENUM('NINJAVAN') NOT NULL
*************************** 2. row ***************************
Field_name: hc_tms.TMS_CARRIER_INF.GRANT_TYPE
Min_value: client_credentials
Max_value: client_credentials
Min_length: 18
Max_length: 18
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 18.0000
Std: NULL
Optimal_fieldtype: ENUM('client_credentials') NOT NULL
2 rows in set, 1 warning (0.01 sec)
以第1行的分析举例,可以看出,
hc_tms.TMS_CARRIER_INF.SYS_INF字段,
Min_value(列最小值):NINJAVAN
Max_value(列最大值):NINJAVAN
Min_length(最小长度):8字节
Max_length(最大长度):8字节
Avg_value_or_avg_length(平均长度):18.0000
Optimal_fieldtype(优化建议):字段的数据类型改成 ENUM('NINJAVAN') NOT NULL。
这些只是建议,表里的数据越来越多时这些建议才会变得准确。最终采不采用MySQL给出的建议,看你心情了