Selecting from view produces syntax error-CodePudding

I have a dumb question. Here is a small database:

CREATE TABLE tmptable (
  `id` INTEGER,
  `p_id` VARCHAR(4)
);

INSERT INTO tmptable
  (`id`, `p_id`)
VALUES
  ('1', 'null'),
  ('2', '1'),
  ('3', '1'),
  ('4', '2'),
  ('5', '2');

Then by submitting:

create view root as
select id from tmptable where p_id is null

select * from root

I got:

Query Error: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select * from root' at line 1

The error is generated by https://www.db-fiddle.com/

My intention was to create a view that can be reused multiple times in one query code script submission. I thought the view would act like a temporary variable, and I wanted to use as little memory as possible. How can I do that?

CodePudding user response：

Creation of view and doing a select on that view after creation is two different queries. Give semicolon after writing the DDL for view.

create view root as
select id from tmptable where p_id is null;

select * from root;

As a result you shouldn't get any record, as the null is actual NULL object and 'null' in the table is a string.