mysql - 将 MySql View 更改为 select 语句

Question

我有一个名为 wallcitiescouncils 的 View ，以及一个从此 View 返回值的过程。我需要更快的响应，因此我想将此 View 更改为 Select 语句，但暂时没有成功。

我需要两个表(城市和议会)的结果，这是我的查看代码:

CREATE OR REPLACE VIEW wallcitiescouncils AS
SELECT
    concat('p',cities.id) as id,
    cities.name as name,
    cities.country_id,
    concat(cities.name,' (',councils.name,'), ',states.name,', ',countries.name) as fullname,
    'City' as type

FROM cities
    JOIN countries  ON (cities.country_id = countries.id)
    JOIN states     ON (cities.state_id = states.id)
    JOIN councils   ON (cities.council_id = councils.id)

UNION ALL

SELECT
    concat('c',councils.id) as id,
    councils.name as name,
    councils.country_id,
    concat(councils.name,'(Council)') as fullname,
    'Council' as type

FROM councils

这是我的程序:

BEGIN
  SELECT name as city, id as city_id, fullname
  FROM wallcitiescouncils
  WHERE country_id = _country_id AND name LIKE CONCAT(search , '%') ORDER BY name LIMIT _limit;
END

我需要使用 Select 语句更改 FROM wallcitiescouncils，这样我就不必调用 View 。

这是我的尝试之一，但结果不正确(我没有理事会结果，并且在“全名”中总是得到相同的理事会名称)，我使用 Councils_id (仅存在于城市表中)来尝试了解如果是城市或市议会:

SELECT
    if(
        cities.council_id is null,
        concat('c',councils.id),
        concat('p',cities.id)
        ) as id,
    if(
        cities.council_id is null,
        councils.name,
        cities.name
        ) as name,

    if(
        cities.council_id is null,
        councils.country_id,
        cities.country_id
        ) as country_id,
    if(
        cities.council_id is null,
        concat(councils.name,' (Municipio)'),
        concat(cities.name,' (',councils.name,'), ',states.name,', ',countries.name)
        )  as fullname
FROM councils, cities
    JOIN countries  ON (country_id = countries.id)
    JOIN states     ON (state_id = states.id)
    /*JOIN councils   ON (cities.council_id = councils.id)*/
where cities.id = 1 or councils.id = 1 limit 200

Answer 1

首先尝试这个查询-

SELECT name as city, id as city_id, fullname FROM (
  SELECT
    concat('p',cities.id) as id,
    cities.name as name,
    cities.country_id,
    concat(cities.name,' (',councils.name,'), ',states.name,', ',countries.name) as fullname,
    'City' as type

  FROM cities
    JOIN countries  ON (cities.country_id = countries.id)
    JOIN states     ON (cities.state_id = states.id)
    JOIN councils   ON (cities.council_id = councils.id)

  UNION ALL

  SELECT
    concat('c',councils.id) as id,
    councils.name as name,
    councils.country_id,
    concat(councils.name,'(Council)') as fullname,
    'Council' as type
  FROM councils
  ) v
WHERE
  v.country_id = _country_id AND v.name LIKE CONCAT(search , '%') ORDER BY v.name LIMIT _limit;