<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="view-restrictions">
      </a>
      27.9 Restrictions on Views
     </h2>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045081117616">
  </a>
  <a class="indexterm" name="idm46045081116128">
  </a>
  <a class="indexterm" name="idm46045081114640">
  </a>
  <a class="indexterm" name="idm46045081113152">
  </a>
  <p>
   The maximum number of tables that can be referenced in the
      definition of a view is 61.
  </p>
  <p>
   View processing is not optimized:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      It is not possible to create an index on a view.
     </p>
    </li>
    <li class="listitem">
     <p>
      Indexes can be used for views processed using the merge
          algorithm. However, a view that is processed with the
          temptable algorithm is unable to take advantage of indexes on
          its underlying tables (although indexes can be used during
          generation of the temporary tables).
     </p>
    </li>
   </ul>
  </div>
  <p>
   There is a general principle that you cannot modify a table and
      select from the same table in a subquery. See
   <a class="xref" href="subquery-restrictions.html" title="15.2.15.12 Restrictions on Subqueries">
    Section 15.2.15.12, “Restrictions on Subqueries”
   </a>
   .
  </p>
  <p>
   The same principle also applies if you select from a view that
      selects from the table, if the view selects from the table in a
      subquery and the view is evaluated using the merge algorithm.
      Example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa98314354"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">VIEW</span> v1 <span class="token keyword">AS</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t2 <span class="token keyword">WHERE</span> <span class="token keyword">EXISTS</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token number">1</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span>a <span class="token operator">=</span> t2<span class="token punctuation">.</span>a<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">UPDATE</span> t1<span class="token punctuation">,</span> v2 <span class="token keyword">SET</span> t1<span class="token punctuation">.</span>a <span class="token operator">=</span> <span class="token number">1</span> <span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span>b <span class="token operator">=</span> v2<span class="token punctuation">.</span>b<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   If the view is evaluated using a temporary table, you
   <span class="emphasis">
    <em>
     can
    </em>
   </span>
   select from the table in the view
      subquery and still modify that table in the outer query. In this
      case, the view is stored in a temporary table and thus you are not
      really selecting from the table in a subquery and modifying it at
      the same time. (This is another reason you might wish to force
      MySQL to use the temptable algorithm by specifying
   <code class="literal">
    ALGORITHM = TEMPTABLE
   </code>
   in the view definition.)
  </p>
  <p>
   You can use
   <a class="link" href="drop-table.html" title="15.1.32 DROP TABLE Statement">
    <code class="literal">
     DROP TABLE
    </code>
   </a>
   or
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   to drop or alter a
      table that is used in a view definition. No warning results from
      the
   <code class="literal">
    DROP
   </code>
   or
   <code class="literal">
    ALTER
   </code>
   operation,
      even though this invalidates the view. Instead, an error occurs
      later, when the view is used.
   <a class="link" href="check-table.html" title="15.7.3.2 CHECK TABLE Statement">
    <code class="literal">
     CHECK
      TABLE
    </code>
   </a>
   can be used to check for views that have been
      invalidated by
   <code class="literal">
    DROP
   </code>
   or
   <code class="literal">
    ALTER
   </code>
   operations.
  </p>
  <p>
   With regard to view updatability, the overall goal for views is
      that if any view is theoretically updatable, it should be
      updatable in practice. Many theoretically updatable views can be
      updated now, but limitations still exist. For details, see
   <a class="xref" href="view-updatability.html" title="27.5.3 Updatable and Insertable Views">
    Section 27.5.3, “Updatable and Insertable Views”
   </a>
   .
  </p>
  <a class="indexterm" name="idm46045081094960">
  </a>
  <a class="indexterm" name="idm46045081093472">
  </a>
  <a class="indexterm" name="idm46045081091984">
  </a>
  <a class="indexterm" name="idm46045081090496">
  </a>
  <a class="indexterm" name="idm46045081089008">
  </a>
  <a class="indexterm" name="idm46045081087520">
  </a>
  <p>
   There exists a shortcoming with the current implementation of
      views. If a user is granted the basic privileges necessary to
      create a view (the
   <a class="link" href="privileges-provided.html#priv_create-view">
    <code class="literal">
     CREATE VIEW
    </code>
   </a>
   and
   <a class="link" href="privileges-provided.html#priv_select">
    <code class="literal">
     SELECT
    </code>
   </a>
   privileges), that user
      cannot call
   <a class="link" href="show-create-view.html" title="15.7.7.14 SHOW CREATE VIEW Statement">
    <code class="literal">
     SHOW CREATE VIEW
    </code>
   </a>
   on
      that object unless the user is also granted the
   <a class="link" href="privileges-provided.html#priv_show-view">
    <code class="literal">
     SHOW VIEW
    </code>
   </a>
   privilege.
  </p>
  <p>
   That shortcoming can lead to problems backing up a database with
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   , which may fail due to insufficient
      privileges. This problem is described in Bug #22062.
  </p>
  <p>
   The workaround to the problem is for the administrator to manually
      grant the
   <a class="link" href="privileges-provided.html#priv_show-view">
    <code class="literal">
     SHOW VIEW
    </code>
   </a>
   privilege to
      users who are granted
   <a class="link" href="privileges-provided.html#priv_create-view">
    <code class="literal">
     CREATE VIEW
    </code>
   </a>
   ,
      since MySQL doesn't grant it implicitly when views are created.
  </p>
  <p>
   Views do not have indexes, so index hints do not apply. Use of
      index hints when selecting from a view is not permitted.
  </p>
  <p>
   <a class="link" href="show-create-view.html" title="15.7.7.14 SHOW CREATE VIEW Statement">
    <code class="literal">
     SHOW CREATE VIEW
    </code>
   </a>
   displays view
      definitions using an
   <code class="literal">
    AS
    <em class="replaceable">
     <code>
      alias_name
     </code>
    </em>
   </code>
   clause for each
      column. If a column is created from an expression, the default
      alias is the expression text, which can be quite long. Aliases for
      column names in
   <a class="link" href="create-view.html" title="15.1.23 CREATE VIEW Statement">
    <code class="literal">
     CREATE VIEW
    </code>
   </a>
   statements are checked against the maximum column length of 64
      characters (not the maximum alias length of 256 characters). As a
      result, views created from the output of
   <a class="link" href="show-create-view.html" title="15.7.7.14 SHOW CREATE VIEW Statement">
    <code class="literal">
     SHOW
      CREATE VIEW
    </code>
   </a>
   fail if any column alias exceeds 64
      characters. This can cause problems in the following circumstances
      for views with too-long aliases:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      View definitions fail to replicate to newer replicas that
          enforce the column-length restriction.
     </p>
    </li>
    <li class="listitem">
     <p>
      Dump files created with
      <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
       <span class="command">
        <strong>
         mysqldump
        </strong>
       </span>
      </a>
      cannot be
          loaded into servers that enforce the column-length
          restriction.
     </p>
    </li>
   </ul>
  </div>
  <p>
   A workaround for either problem is to modify each problematic view
      definition to use aliases that provide shorter column names. Then
      the view replicates properly, and can be dumped and reloaded
      without causing an error. To modify the definition, drop and
      create the view again with
   <a class="link" href="drop-view.html" title="15.1.35 DROP VIEW Statement">
    <code class="literal">
     DROP
      VIEW
    </code>
   </a>
   and
   <a class="link" href="create-view.html" title="15.1.23 CREATE VIEW Statement">
    <code class="literal">
     CREATE VIEW
    </code>
   </a>
   , or
      replace the definition with
   <a class="link" href="create-view.html" title="15.1.23 CREATE VIEW Statement">
    <code class="literal">
     CREATE OR REPLACE
      VIEW
    </code>
   </a>
   .
  </p>
  <p>
   For problems that occur when reloading view definitions in dump
      files, another workaround is to edit the dump file to modify its
   <a class="link" href="create-view.html" title="15.1.23 CREATE VIEW Statement">
    <code class="literal">
     CREATE VIEW
    </code>
   </a>
   statements. However,
      this does not change the original view definitions, which may
      cause problems for subsequent dump operations.
  </p>
 </div>
 <br/>
</div>
