MySQL Forums
Forum List  »  Backup

Re: Backup and restore for views
Posted by: Peter Brawley
Date: June 16, 2022 08:36PM

In MySQL, Views behave somewhat differently than do stored queries.

In 8.0.29, after ...

use test;
drop table if exists a,va;
create table a(i int primary key,b int);
insert into a values(1,2);
create view va as select * from a;

... the OS command ...

mysqldump -u... -p... -h... test a va

... produces ...

DROP TABLE IF EXISTS `a`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `a` (
  `i` int NOT NULL,
  `b` int DEFAULT NULL,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
...
LOCK TABLES `a` WRITE;
/*!40000 ALTER TABLE `a` DISABLE KEYS */;
INSERT INTO `a` VALUES (1,2);
/*!40000 ALTER TABLE `a` ENABLE KEYS */;
UNLOCK TABLES;
...
DROP TABLE IF EXISTS `va`;
/*!50001 DROP VIEW IF EXISTS `va`*/;
/*!50001 CREATE VIEW `va` AS SELECT
 1 AS `i`,
 1 AS `b`*/;
...
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`...`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `va` AS select `a`.`i` AS `i`,`a`.`b` AS `b` from `a` */;

..., that is, MySQL code to accurately recreate the table & view as I created them. You get something different?

Options: ReplyQuote


Subject
Views
Written By
Posted
332
March 15, 2022 08:02AM
157
March 15, 2022 11:23AM
Re: Backup and restore for views
92
June 16, 2022 08:36PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.