Mysql relational database management system
MySQL is an open source small relational database management system developed by the Swedish MySQL AB company. MySQL is widely used in small and medium-sized websites on the Internet. Due to its small size, fast speed, low total cost of ownership, and especially the characteristics of open source, many small and medium-sized websites choose MySQL as their website database in order to reduce the total cost of website ownership.
I don’t know if you have ever been depressed about what type of fields to choose for the MySQL table structure? Or are you confused about what length to choose for a MySQL field? In the following article, I will introduce to you a martial arts secret that I only learned recently. Friends who are interested can take a look below.
Introduction
The procedure analyze() function is MySQL’s built-in field type that performs statistical analysis on MySQL field values and gives suggestions.
Syntax
procesure analyse(max_elements,max_memory)
max_elements
Specifies the maximum value of non-duplicate values in each column. When this value is exceeded, MySQL will not recommend the enum type.
max_memory
analyse() finds the maximum memory size used for all distinct values for each column.
Practical Exercise
# 对t1表所有的列进行分析 wing@3306>show create table t1; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` varchar(16) DEFAULT NULL, `score` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) wing@3306>select * from t1 procedure analyse(4); +---------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+-------------+---------------------------+ | Field_name | Min_value | Max_value | Min_length | Max_length | Empties_or_zeros | Nulls | Avg_value_or_avg_length | Std | Optimal_fieldtype | +---------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+-------------+---------------------------+ | wing.t1.id | 1 | 200000 | 1 | 6 | 0 | 0 | 100000.5000 | 116099.2790 | MEDIUMINT(6) UNSIGNED NOT NULL | | wing.t1.name | 000jxc6V | zzznmkcX | 8 | 8 | 0 | 0 | 8.0000 | NULL | CHAR(8) NOT NULL | | wing.t1.score | 1 | 100 | 1 | 3 | 0 | 0 | 50.4889 | 28.8768 | TINYINT(3) UNSIGNED NOT NULL | +---------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+-------------+---------------------------+ 3 rows in set (0.14 sec)
Summary
The above is all about the procedure analyze() function in MySQL. For more related content, please pay attention to the PHP Chinese website ( www.php.cn)!