Create comments for views in mysql
Solution 1
According to the create view syntax there is no way currently to add comment a view:
This feature has been requested several times. There are four active tickets related to this functionality:
- http://bugs.mysql.com/bug.php?id=5159
- http://bugs.mysql.com/bug.php?id=64045
- http://bugs.mysql.com/bug.php?id=52429
- http://bugs.mysql.com/bug.php?id=15344
...and several marked as duplicates: http://bugs.mysql.com/bug.php?id=19602 , http://bugs.mysql.com/bug.php?id=19602 , http://bugs.mysql.com/bug.php?id=13109 , http://bugs.mysql.com/bug.php?id=14369 , http://bugs.mysql.com/bug.php?id=11082 , http://bugs.mysql.com/bug.php?id=42870 , http://bugs.mysql.com/bug.php?id=38137 , http://bugs.mysql.com/bug.php?id=38137 , http://bugs.mysql.com/bug.php?id=30729
If you are interested in this issue, go to the four active tickets, click the "affects me" button, and also add a comment, asking if anyone is working on this feature.
This will add visibility, and increase the likelyhood of it being implemented.
Solution 2
I had a similar need, and one way I hacked this in MySQL was to add a truthy predicate in the WHERE
clause that served as documentation. I admit this is hacky, but wouldn't you agree any documentation is better than no documentation at all? Once nice side-effect of doing your commentary this way will survive a mysqldump
. As far as I know, the optimizer will not be hindered by the extra truthy predicate.
Example view creation:
CREATE OR REPLACE VIEW high_value_employees AS
SELECT *
FROM `employees`
WHERE salary >= 200000
AND 'comment' != 'This view was made by Josh at the request of an important VP who wanted a concise list of who we might be overpaying. Last modified on 26 July 2019.';
And then viewing the documentation ...
> SHOW CREATE TABLE high_value_employees \G
*************************** 1. row ***************************
View: high_value_employees
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`jhuber`@`%` SQL SECURITY
DEFINER VIEW `high_value_employees` AS select `employees`.`salary` AS `salary` from
`employees` where ((`employees`.`salary` >= 200000) and ('comment' <> 'This view was
made by Josh at the request of an important VP who wanted a concise list of who we
might be overpaying. Last modified on 26 July 2019.'))
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
1 row in set (0.00 sec)
Elzo Valugi
occupation: Professional websurfer blog: valugi.ro My first computer was a HC91, connected to a black and white TV and magnetophone.
Updated on June 05, 2022Comments
-
Elzo Valugi about 2 years
I see that the views have a comment field just like the regular tables, but is by default populated with the "VIEW" value.
[TABLE_CATALOG] => [TABLE_SCHEMA] => xxx [TABLE_NAME] => view__xxxx [TABLE_TYPE] => VIEW [ENGINE] => [VERSION] => [ROW_FORMAT] => [TABLE_ROWS] => [AVG_ROW_LENGTH] => [DATA_LENGTH] => [MAX_DATA_LENGTH] => [INDEX_LENGTH] => [DATA_FREE] => [AUTO_INCREMENT] => [CREATE_TIME] => [UPDATE_TIME] => [CHECK_TIME] => [TABLE_COLLATION] => [CHECKSUM] => [CREATE_OPTIONS] => [TABLE_COMMENT] => VIEW
When I am trying to create a view with a comment I get an error.
CREATE OR REPLACE VIEW view__x AS SELECT * FROM `some_table` COMMENT = 'some comment'
Is there a way to modify the comment field or that field is used internally for something else and should stay like it is?
I've added a feature request to mysql.