430/connector/mysql.html


<!DOCTYPE html>

<html lang="en">
  <head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" /><meta name="generator" content="Docutils 0.18.1: http://docutils.sourceforge.net/" />

  <meta name="viewport" content="width=device-width,initial-scale=1">
  <meta http-equiv="x-ua-compatible" content="ie=edge">
  <meta name="lang:clipboard.copy" content="Copy to clipboard">
  <meta name="lang:clipboard.copied" content="Copied to clipboard">
  <meta name="lang:search.language" content="en">
  <meta name="lang:search.pipeline.stopwords" content="True">
  <meta name="lang:search.pipeline.trimmer" content="True">
  <meta name="lang:search.result.none" content="No matching documents">
  <meta name="lang:search.result.one" content="1 matching document">
  <meta name="lang:search.result.other" content="# matching documents">
  <meta name="lang:search.tokenizer" content="[\s\-]+">

  
    <link href="https://fonts.gstatic.com/" rel="preconnect" crossorigin>
    <link href="https://fonts.googleapis.com/css?family=Roboto+Mono:400,500,700|Roboto:300,400,400i,700&display=fallback" rel="stylesheet">

    <link href="https://fonts.googleapis.com/css2?family=Alata&family=Open+Sans:ital,wght%400,300;0,400;0,600;1,400&display=swap" rel="stylesheet">
    <style>
      body,
      input {
        font-family: "Roboto", "Helvetica Neue", Helvetica, Arial, sans-serif
      }

      code,
      kbd,
      pre {
        font-family: "Roboto Mono", "Courier New", Courier, monospace
      }
    </style>
  

  <link rel="stylesheet" href="../_static/stylesheets/application.css"/>
  <link rel="stylesheet" href="../_static/stylesheets/application-palette.css"/>
  <link rel="stylesheet" href="../_static/stylesheets/application-fixes.css"/>
  
  <link rel="stylesheet" href="../_static/fonts/material-icons.css"/>
  
  <meta name="theme-color" content="2196f3">
  <script src="../_static/javascripts/modernizr.js"></script>
  
  
    <title>MySQL connector &#8212; Trino 430 Documentation</title>
    <link rel="stylesheet" type="text/css" href="../_static/pygments.css" />
    <link rel="stylesheet" type="text/css" href="../_static/material.css" />
    <link rel="stylesheet" type="text/css" href="../_static/copybutton.css" />
    <link rel="stylesheet" type="text/css" href="../_static/trino.css" />
    <script data-url_root="../" id="documentation_options" src="../_static/documentation_options.js"></script>
    <script src="../_static/jquery.js"></script>
    <script src="../_static/underscore.js"></script>
    <script src="../_static/_sphinx_javascript_frameworks_compat.js"></script>
    <script src="../_static/doctools.js"></script>
    <script src="../_static/clipboard.min.js"></script>
    <script src="../_static/copybutton.js"></script>
    <link rel="index" title="Index" href="../genindex.html" />
    <link rel="search" title="Search" href="../search.html" />
    <link rel="next" title="Oracle connector" href="oracle.html" />
    <link rel="prev" title="MongoDB connector" href="mongodb.html" />
  
   
    <script async src="https://www.googletagmanager.com/gtag/js?id=G-RJ94STKPJ5"></script>
    <script>
      window.dataLayer = window.dataLayer || [];
      function gtag(){dataLayer.push(arguments);}
      gtag('js', new Date());
      gtag('config', 'G-RJ94STKPJ5');
      gtag('config', 'UA-133457846-1');
      gtag('config', 'AW-1036784065');
      window._linkedin_data_partner_ids = ['2842796'];
    </script>
    <script async src="https://snap.licdn.com/li.lms-analytics/insight.min.js"></script>
    <link rel=stylesheet href=https://use.fontawesome.com/releases/v5.8.2/css/all.css>
  </head>
  <body dir=ltr
        data-md-color-primary= data-md-color-accent=>
  
  <svg class="md-svg">
    <defs data-children-count="0">
      
      <svg xmlns="http://www.w3.org/2000/svg" width="416" height="448" viewBox="0 0 416 448" id="__github"><path fill="currentColor" d="M160 304q0 10-3.125 20.5t-10.75 19T128 352t-18.125-8.5-10.75-19T96 304t3.125-20.5 10.75-19T128 256t18.125 8.5 10.75 19T160 304zm160 0q0 10-3.125 20.5t-10.75 19T288 352t-18.125-8.5-10.75-19T256 304t3.125-20.5 10.75-19T288 256t18.125 8.5 10.75 19T320 304zm40 0q0-30-17.25-51T296 232q-10.25 0-48.75 5.25Q229.5 240 208 240t-39.25-2.75Q130.75 232 120 232q-29.5 0-46.75 21T56 304q0 22 8 38.375t20.25 25.75 30.5 15 35 7.375 37.25 1.75h42q20.5 0 37.25-1.75t35-7.375 30.5-15 20.25-25.75T360 304zm56-44q0 51.75-15.25 82.75-9.5 19.25-26.375 33.25t-35.25 21.5-42.5 11.875-42.875 5.5T212 416q-19.5 0-35.5-.75t-36.875-3.125-38.125-7.5-34.25-12.875T37 371.5t-21.5-28.75Q0 312 0 260q0-59.25 34-99-6.75-20.5-6.75-42.5 0-29 12.75-54.5 27 0 47.5 9.875t47.25 30.875Q171.5 96 212 96q37 0 70 8 26.25-20.5 46.75-30.25T376 64q12.75 25.5 12.75 54.5 0 21.75-6.75 42 34 40 34 99.5z"/></svg>
      
    </defs>
  </svg>
  
  <input class="md-toggle" data-md-toggle="drawer" type="checkbox" id="__drawer">
  <input class="md-toggle" data-md-toggle="search" type="checkbox" id="__search">
  <label class="md-overlay" data-md-component="overlay" for="__drawer"></label>
  <a href="#connector/mysql" tabindex="1" class="md-skip"> Skip to content </a>
  <div id="announcement">
  <div id="announcement-content">
    <a href="https://github.com/trinodb/trino" target="_blank">
      Do you ❤️ Trino? Give us a 🌟 on GitHub <i class="fab fa-github"></i>
    </a>
  </div>
</div>
<header class="md-header" data-md-component="header">
  <nav class="md-header-nav md-grid">
    <div class="md-flex navheader">
      <div class="md-flex__cell md-flex__cell--shrink">
        <a href="/" title="Trino"
           class="md-header-nav__button md-logo">
          
              <img src="../_static/trino.svg" height="26"
                   alt="Trino 430 Documentation logo">
          
        </a>
      </div>
      <div class="md-flex__cell md-flex__cell--shrink">
        <label class="md-icon md-icon--menu md-header-nav__button" for="__drawer"></label>
      </div>
      <div class="md-flex__cell md-flex__cell--stretch">
        <div class="md-flex__ellipsis md-header-nav__title" data-md-component="title">
          <span class="md-header-nav__topic">Trino 430 Documentation</span>
          <span class="md-header-nav__topic"> MySQL connector </span>
        </div>
      </div>
      <div class="md-flex__cell md-flex__cell--shrink">
        <label class="md-icon md-icon--search md-header-nav__button" for="__search"></label>
        
<div class="md-search" data-md-component="search" role="dialog">
  <label class="md-search__overlay" for="__search"></label>
  <div class="md-search__inner" role="search">
    <form class="md-search__form" action="../search.html" method="GET" name="search">
      <input type="text" class="md-search__input" name="q" placeholder="Search"
             autocapitalize="off" autocomplete="off" spellcheck="false"
             data-md-component="query" data-md-state="active">
      <label class="md-icon md-search__icon" for="__search"></label>
      <button type="reset" class="md-icon md-search__icon" data-md-component="reset" tabindex="-1">
        &#xE5CD;
      </button>
    </form>
    <div class="md-search__output">
      <div class="md-search__scrollwrap" data-md-scrollfix>
        <div class="md-search-result" data-md-component="result">
          <div class="md-search-result__meta">
            Type to start searching
          </div>
          <ol class="md-search-result__list"></ol>
        </div>
      </div>
    </div>
  </div>
</div>

      </div>
      
        <div class="md-flex__cell md-flex__cell--shrink">
          <div class="md-header-nav__source">
            <a href="https://github.com/trinodb/trino" title="Go to repository" class="md-source" data-md-source="github">

    <div class="md-source__icon">
      <svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 24 24" width="28" height="28">
        <use xlink:href="#__github" width="24" height="24"></use>
      </svg>
    </div>
  
  <div class="md-source__repository">
    Trino
  </div>
</a>
          </div>
        </div>
      
      
  <script src="../_static/javascripts/version_dropdown.js"></script>
  <script>
    var json_loc = "../../versions.json",
        target_loc = "../../",
        text = "Versions";
    $( document ).ready( add_version_dropdown(json_loc, target_loc, text));
  </script>
  

    </div>
  </nav>
</header>

  
  <div class="md-container">
    
    
    <!-- empty -->
    <main class="md-main">
      <div class="md-main__inner md-grid" data-md-component="container">
        
          <div class="md-sidebar md-sidebar--primary" data-md-component="navigation">
            <div class="md-sidebar__scrollwrap">
              <div class="md-sidebar__inner">
                <nav class="md-nav md-nav--primary" data-md-level="0">
  <label class="md-nav__title md-nav__title--site" for="__drawer">
    <a href="/" title="Trino" class="md-nav__button md-logo">
      
        <img src="../_static/trino.svg" alt=" logo" width="48" height="48">
      
    </a>
    <a href="../index.html"
       title="Trino 430 Documentation">Trino 430 Documentation</a>
  </label>
    <div class="md-nav__source">
      <a href="https://github.com/trinodb/trino" title="Go to repository" class="md-source" data-md-source="github">

    <div class="md-source__icon">
      <svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 24 24" width="28" height="28">
        <use xlink:href="#__github" width="24" height="24"></use>
      </svg>
    </div>
  
  <div class="md-source__repository">
    Trino
  </div>
</a>
    </div>
  
  
  <ul class="md-nav__list">
    <li class="md-nav__item">
    
    
      <a href="../overview.html" class="md-nav__link">Overview</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../installation.html" class="md-nav__link">Installation</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../client.html" class="md-nav__link">Clients</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../security.html" class="md-nav__link">Security</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../admin.html" class="md-nav__link">Administration</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../optimizer.html" class="md-nav__link">Query optimizer</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../connector.html" class="md-nav__link">Connectors</a>
      <ul class="md-nav__list"> 
    <li class="md-nav__item">
    
    
      <a href="accumulo.html" class="md-nav__link">Accumulo</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="atop.html" class="md-nav__link">Atop</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="bigquery.html" class="md-nav__link">BigQuery</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="blackhole.html" class="md-nav__link">Black Hole</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="cassandra.html" class="md-nav__link">Cassandra</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="clickhouse.html" class="md-nav__link">ClickHouse</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="delta-lake.html" class="md-nav__link">Delta Lake</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="druid.html" class="md-nav__link">Druid</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="elasticsearch.html" class="md-nav__link">Elasticsearch</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="googlesheets.html" class="md-nav__link">Google Sheets</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="hive.html" class="md-nav__link">Hive</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="hudi.html" class="md-nav__link">Hudi</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="iceberg.html" class="md-nav__link">Iceberg</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="ignite.html" class="md-nav__link">Ignite</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="jmx.html" class="md-nav__link">JMX</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="kafka.html" class="md-nav__link">Kafka</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="kinesis.html" class="md-nav__link">Kinesis</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="kudu.html" class="md-nav__link">Kudu</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="localfile.html" class="md-nav__link">Local File</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="mariadb.html" class="md-nav__link">MariaDB</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="memory.html" class="md-nav__link">Memory</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="mongodb.html" class="md-nav__link">MongoDB</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
    <input class="md-toggle md-nav__toggle" data-md-toggle="toc" type="checkbox" id="__toc">
    <label class="md-nav__link md-nav__link--active" for="__toc"> MySQL </label>
    
      <a href="#" class="md-nav__link md-nav__link--active">MySQL</a>
      
        
<nav class="md-nav md-nav--secondary">
    <label class="md-nav__title" for="__toc">Contents</label>
    <ul class="md-nav__list" data-md-scrollfix="">
        <li class="md-nav__item"><a href="#requirements" class="md-nav__link">Requirements</a>
        </li>
        <li class="md-nav__item"><a href="#configuration" class="md-nav__link">Configuration</a><nav class="md-nav">
                <ul class="md-nav__list">
        <li class="md-nav__item"><a href="#connection-security" class="md-nav__link">Connection security</a>
        </li>
        <li class="md-nav__item"><a href="#data-source-authentication" class="md-nav__link">Data source authentication</a>
        </li>
        <li class="md-nav__item"><a href="#multiple-mysql-servers" class="md-nav__link">Multiple MySQL servers</a>
        </li>
        <li class="md-nav__item"><a href="#general-configuration-properties" class="md-nav__link">General configuration properties</a>
        </li>
        <li class="md-nav__item"><a href="#appending-query-metadata" class="md-nav__link">Appending query metadata</a>
        </li>
        <li class="md-nav__item"><a href="#domain-compaction-threshold" class="md-nav__link">Domain compaction threshold</a>
        </li>
        <li class="md-nav__item"><a href="#procedures" class="md-nav__link">Procedures</a>
        </li>
        <li class="md-nav__item"><a href="#case-insensitive-matching" class="md-nav__link">Case insensitive matching</a>
        </li>
        <li class="md-nav__item"><a href="#non-transactional-insert" class="md-nav__link">Non-transactional INSERT</a>
        </li></ul>
            </nav>
        </li>
        <li class="md-nav__item"><a href="#type-mapping" class="md-nav__link">Type mapping</a><nav class="md-nav">
                <ul class="md-nav__list">
        <li class="md-nav__item"><a href="#mysql-to-trino-type-mapping" class="md-nav__link">MySQL to Trino type mapping</a>
        </li>
        <li class="md-nav__item"><a href="#trino-to-mysql-type-mapping" class="md-nav__link">Trino to MySQL type mapping</a>
        </li>
        <li class="md-nav__item"><a href="#timestamp-type-handling" class="md-nav__link">Timestamp type handling</a>
        </li>
        <li class="md-nav__item"><a href="#decimal-type-handling" class="md-nav__link">Decimal type handling</a>
        </li>
        <li class="md-nav__item"><a href="#type-mapping-configuration-properties" class="md-nav__link">Type mapping configuration properties</a>
        </li></ul>
            </nav>
        </li>
        <li class="md-nav__item"><a href="#querying-mysql" class="md-nav__link">Querying MySQL</a>
        </li>
        <li class="md-nav__item"><a href="#sql-support" class="md-nav__link">SQL support</a><nav class="md-nav">
                <ul class="md-nav__list">
        <li class="md-nav__item"><a href="#update" class="md-nav__link">UPDATE</a>
        </li>
        <li class="md-nav__item"><a href="#sql-delete" class="md-nav__link">SQL DELETE</a>
        </li></ul>
            </nav>
        </li>
        <li class="md-nav__item"><a href="#fault-tolerant-execution-support" class="md-nav__link">Fault-tolerant execution support</a>
        </li>
        <li class="md-nav__item"><a href="#table-functions" class="md-nav__link">Table functions</a><nav class="md-nav">
                <ul class="md-nav__list">
        <li class="md-nav__item"><a href="#query-varchar-table" class="md-nav__link"><code class="docutils literal notranslate"><span class="pre">query(varchar)</span> <span class="pre">-&gt;</span> <span class="pre">table</span></code></a>
        </li></ul>
            </nav>
        </li>
        <li class="md-nav__item"><a href="#performance" class="md-nav__link">Performance</a><nav class="md-nav">
                <ul class="md-nav__list">
        <li class="md-nav__item"><a href="#table-statistics" class="md-nav__link">Table statistics</a>
        </li>
        <li class="md-nav__item"><a href="#pushdown" class="md-nav__link">Pushdown</a><nav class="md-nav">
                <ul class="md-nav__list">
        <li class="md-nav__item"><a href="#cost-based-join-pushdown" class="md-nav__link">Cost-based join pushdown</a>
        </li>
        <li class="md-nav__item"><a href="#predicate-pushdown-support" class="md-nav__link">Predicate pushdown support</a>
        </li></ul>
            </nav>
        </li></ul>
            </nav>
        </li>
    </ul>
</nav>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="oracle.html" class="md-nav__link">Oracle</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="phoenix.html" class="md-nav__link">Phoenix</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="pinot.html" class="md-nav__link">Pinot</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="postgresql.html" class="md-nav__link">PostgreSQL</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="prometheus.html" class="md-nav__link">Prometheus</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="redis.html" class="md-nav__link">Redis</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="redshift.html" class="md-nav__link">Redshift</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="singlestore.html" class="md-nav__link">SingleStore</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="sqlserver.html" class="md-nav__link">SQL Server</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="system.html" class="md-nav__link">System</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="thrift.html" class="md-nav__link">Thrift</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="tpcds.html" class="md-nav__link">TPCDS</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="tpch.html" class="md-nav__link">TPCH</a>
      
    
    </li></ul>
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../functions.html" class="md-nav__link">Functions and operators</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../language.html" class="md-nav__link">SQL language</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../sql.html" class="md-nav__link">SQL statement syntax</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../develop.html" class="md-nav__link">Developer guide</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../glossary.html" class="md-nav__link">Glossary</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../appendix.html" class="md-nav__link">Appendix</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../release.html" class="md-nav__link">Release notes</a>
      
    
    </li>
  </ul>
  

</nav>
              </div>
            </div>
          </div>
          <div class="md-sidebar md-sidebar--secondary" data-md-component="toc">
            <div class="md-sidebar__scrollwrap">
              <div class="md-sidebar__inner">
                
<nav class="md-nav md-nav--secondary">
    <label class="md-nav__title" for="__toc">Contents</label>
    <ul class="md-nav__list" data-md-scrollfix="">
        <li class="md-nav__item"><a href="#requirements" class="md-nav__link">Requirements</a>
        </li>
        <li class="md-nav__item"><a href="#configuration" class="md-nav__link">Configuration</a><nav class="md-nav">
                <ul class="md-nav__list">
        <li class="md-nav__item"><a href="#connection-security" class="md-nav__link">Connection security</a>
        </li>
        <li class="md-nav__item"><a href="#data-source-authentication" class="md-nav__link">Data source authentication</a>
        </li>
        <li class="md-nav__item"><a href="#multiple-mysql-servers" class="md-nav__link">Multiple MySQL servers</a>
        </li>
        <li class="md-nav__item"><a href="#general-configuration-properties" class="md-nav__link">General configuration properties</a>
        </li>
        <li class="md-nav__item"><a href="#appending-query-metadata" class="md-nav__link">Appending query metadata</a>
        </li>
        <li class="md-nav__item"><a href="#domain-compaction-threshold" class="md-nav__link">Domain compaction threshold</a>
        </li>
        <li class="md-nav__item"><a href="#procedures" class="md-nav__link">Procedures</a>
        </li>
        <li class="md-nav__item"><a href="#case-insensitive-matching" class="md-nav__link">Case insensitive matching</a>
        </li>
        <li class="md-nav__item"><a href="#non-transactional-insert" class="md-nav__link">Non-transactional INSERT</a>
        </li></ul>
            </nav>
        </li>
        <li class="md-nav__item"><a href="#type-mapping" class="md-nav__link">Type mapping</a><nav class="md-nav">
                <ul class="md-nav__list">
        <li class="md-nav__item"><a href="#mysql-to-trino-type-mapping" class="md-nav__link">MySQL to Trino type mapping</a>
        </li>
        <li class="md-nav__item"><a href="#trino-to-mysql-type-mapping" class="md-nav__link">Trino to MySQL type mapping</a>
        </li>
        <li class="md-nav__item"><a href="#timestamp-type-handling" class="md-nav__link">Timestamp type handling</a>
        </li>
        <li class="md-nav__item"><a href="#decimal-type-handling" class="md-nav__link">Decimal type handling</a>
        </li>
        <li class="md-nav__item"><a href="#type-mapping-configuration-properties" class="md-nav__link">Type mapping configuration properties</a>
        </li></ul>
            </nav>
        </li>
        <li class="md-nav__item"><a href="#querying-mysql" class="md-nav__link">Querying MySQL</a>
        </li>
        <li class="md-nav__item"><a href="#sql-support" class="md-nav__link">SQL support</a><nav class="md-nav">
                <ul class="md-nav__list">
        <li class="md-nav__item"><a href="#update" class="md-nav__link">UPDATE</a>
        </li>
        <li class="md-nav__item"><a href="#sql-delete" class="md-nav__link">SQL DELETE</a>
        </li></ul>
            </nav>
        </li>
        <li class="md-nav__item"><a href="#fault-tolerant-execution-support" class="md-nav__link">Fault-tolerant execution support</a>
        </li>
        <li class="md-nav__item"><a href="#table-functions" class="md-nav__link">Table functions</a><nav class="md-nav">
                <ul class="md-nav__list">
        <li class="md-nav__item"><a href="#query-varchar-table" class="md-nav__link"><code class="docutils literal notranslate"><span class="pre">query(varchar)</span> <span class="pre">-&gt;</span> <span class="pre">table</span></code></a>
        </li></ul>
            </nav>
        </li>
        <li class="md-nav__item"><a href="#performance" class="md-nav__link">Performance</a><nav class="md-nav">
                <ul class="md-nav__list">
        <li class="md-nav__item"><a href="#table-statistics" class="md-nav__link">Table statistics</a>
        </li>
        <li class="md-nav__item"><a href="#pushdown" class="md-nav__link">Pushdown</a><nav class="md-nav">
                <ul class="md-nav__list">
        <li class="md-nav__item"><a href="#cost-based-join-pushdown" class="md-nav__link">Cost-based join pushdown</a>
        </li>
        <li class="md-nav__item"><a href="#predicate-pushdown-support" class="md-nav__link">Predicate pushdown support</a>
        </li></ul>
            </nav>
        </li></ul>
            </nav>
        </li>
    </ul>
</nav>
              </div>
            </div>
          </div>
        
        <div class="md-content">
          <article class="md-content__inner md-typeset" role="main">
            
  <section id="mysql-connector">
<h1 id="connector-mysql--page-root">MySQL connector<a class="headerlink" href="#connector-mysql--page-root" title="Permalink to this heading">#</a></h1>
<img class="connector-logo" src="../_static/img/mysql.png"/><p>The MySQL connector allows querying and creating tables in an external
<a class="reference external" href="https://www.mysql.com/">MySQL</a> instance. This can be used to join data between different
systems like MySQL and Hive, or between two different MySQL instances.</p>
<section id="requirements">
<h2 id="requirements">Requirements<a class="headerlink" href="#requirements" title="Permalink to this heading">#</a></h2>
<p>To connect to MySQL, you need:</p>
<ul class="simple">
<li><p>MySQL 5.7, 8.0 or higher.</p></li>
<li><p>Network access from the Trino coordinator and workers to MySQL.
Port 3306 is the default port.</p></li>
</ul>
</section>
<section id="configuration">
<h2 id="configuration">Configuration<a class="headerlink" href="#configuration" title="Permalink to this heading">#</a></h2>
<p>To configure the MySQL connector, create a catalog properties file in
<code class="docutils literal notranslate"><span class="pre">etc/catalog</span></code> named, for example, <code class="docutils literal notranslate"><span class="pre">example.properties</span></code>, to mount the MySQL
connector as the <code class="docutils literal notranslate"><span class="pre">mysql</span></code> catalog. Create the file with the following contents,
replacing the connection properties as appropriate for your setup:</p>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>connector.name=mysql
connection-url=jdbc:mysql://example.net:3306
connection-user=root
connection-password=secret
</pre></div>
</div>
<p>The <code class="docutils literal notranslate"><span class="pre">connection-url</span></code> defines the connection information and parameters to pass
to the MySQL JDBC driver. The supported parameters for the URL are
available in the <a class="reference external" href="https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html">MySQL Developer Guide</a>.</p>
<p>For example, the following <code class="docutils literal notranslate"><span class="pre">connection-url</span></code> allows you to require encrypted
connections to the MySQL server:</p>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>connection-url=jdbc:mysql://example.net:3306?sslMode=REQUIRED
</pre></div>
</div>
<p>The <code class="docutils literal notranslate"><span class="pre">connection-user</span></code> and <code class="docutils literal notranslate"><span class="pre">connection-password</span></code> are typically required and
determine the user credentials for the connection, often a service user. You can
use <a class="reference internal" href="../security/secrets.html"><span class="doc">secrets</span></a> to avoid actual values in the catalog
properties files.</p>
<section id="connection-security">
<span id="mysql-tls"></span><h3 id="connection-security">Connection security<a class="headerlink" href="#connection-security" title="Permalink to this heading">#</a></h3>
<p>If you have TLS configured with a globally-trusted certificate installed on your
data source, you can enable TLS between your cluster and the data
source by appending a parameter to the JDBC connection string set in the
<code class="docutils literal notranslate"><span class="pre">connection-url</span></code> catalog configuration property.</p>
<p>For example, with version 8.0 of MySQL Connector/J, use the <code class="docutils literal notranslate"><span class="pre">sslMode</span></code>
parameter to secure the connection with TLS. By default the parameter is set to
<code class="docutils literal notranslate"><span class="pre">PREFERRED</span></code> which secures the connection if enabled by the server. You can
also set this parameter to <code class="docutils literal notranslate"><span class="pre">REQUIRED</span></code> which causes the connection to fail if
TLS is not established.</p>
<p>You can set the <code class="docutils literal notranslate"><span class="pre">sslMode</span></code> parameter in the catalog configuration file by
appending it to the <code class="docutils literal notranslate"><span class="pre">connection-url</span></code> configuration property:</p>
<div class="highlight-properties notranslate"><div class="highlight"><pre><span></span><span class="na">connection-url</span><span class="o">=</span><span class="s">jdbc:mysql://example.net:3306/?sslMode=REQUIRED</span>
</pre></div>
</div>
<p>For more information on TLS configuration options, see the <a class="reference external" href="https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-connp-props-security.html#cj-conn-prop_sslMode">MySQL JDBC security
documentation</a>.</p>
</section>
<section id="data-source-authentication">
<h3 id="data-source-authentication">Data source authentication<a class="headerlink" href="#data-source-authentication" title="Permalink to this heading">#</a></h3>
<p>The connector can provide credentials for the data source connection
in multiple ways:</p>
<ul class="simple">
<li><p>inline, in the connector configuration file</p></li>
<li><p>in a separate properties file</p></li>
<li><p>in a key store file</p></li>
<li><p>as extra credentials set when connecting to Trino</p></li>
</ul>
<p>You can use <a class="reference internal" href="../security/secrets.html"><span class="doc">secrets</span></a> to avoid storing sensitive
values in the catalog properties files.</p>
<p>The following table describes configuration properties
for connection credentials:</p>
<table>
<colgroup>
<col style="width: 40%"/>
<col style="width: 60%"/>
</colgroup>
<thead>
<tr class="row-odd"><th class="head"><p>Property name</p></th>
<th class="head"><p>Description</p></th>
</tr>
</thead>
<tbody>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">credential-provider.type</span></code></p></td>
<td><p>Type of the credential provider. Must be one of <code class="docutils literal notranslate"><span class="pre">INLINE</span></code>, <code class="docutils literal notranslate"><span class="pre">FILE</span></code>, or
<code class="docutils literal notranslate"><span class="pre">KEYSTORE</span></code>; defaults to <code class="docutils literal notranslate"><span class="pre">INLINE</span></code>.</p></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">connection-user</span></code></p></td>
<td><p>Connection user name.</p></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">connection-password</span></code></p></td>
<td><p>Connection password.</p></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">user-credential-name</span></code></p></td>
<td><p>Name of the extra credentials property, whose value to use as the user
name. See <code class="docutils literal notranslate"><span class="pre">extraCredentials</span></code> in <a class="reference internal" href="../client/jdbc.html#jdbc-parameter-reference"><span class="std std-ref">Parameter
reference</span></a>.</p></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">password-credential-name</span></code></p></td>
<td><p>Name of the extra credentials property, whose value to use as the
password.</p></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">connection-credential-file</span></code></p></td>
<td><p>Location of the properties file where credentials are present. It must
contain the <code class="docutils literal notranslate"><span class="pre">connection-user</span></code> and <code class="docutils literal notranslate"><span class="pre">connection-password</span></code> properties.</p></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">keystore-file-path</span></code></p></td>
<td><p>The location of the Java Keystore file, from which to read credentials.</p></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">keystore-type</span></code></p></td>
<td><p>File format of the keystore file, for example <code class="docutils literal notranslate"><span class="pre">JKS</span></code> or <code class="docutils literal notranslate"><span class="pre">PEM</span></code>.</p></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">keystore-password</span></code></p></td>
<td><p>Password for the key store.</p></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">keystore-user-credential-name</span></code></p></td>
<td><p>Name of the key store entity to use as the user name.</p></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">keystore-user-credential-password</span></code></p></td>
<td><p>Password for the user name key store entity.</p></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">keystore-password-credential-name</span></code></p></td>
<td><p>Name of the key store entity to use as the password.</p></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">keystore-password-credential-password</span></code></p></td>
<td><p>Password for the password key store entity.</p></td>
</tr>
</tbody>
</table>
</section>
<section id="multiple-mysql-servers">
<h3 id="multiple-mysql-servers">Multiple MySQL servers<a class="headerlink" href="#multiple-mysql-servers" title="Permalink to this heading">#</a></h3>
<p>You can have as many catalogs as you need, so if you have additional
MySQL servers, simply add another properties file to <code class="docutils literal notranslate"><span class="pre">etc/catalog</span></code>
with a different name, making sure it ends in <code class="docutils literal notranslate"><span class="pre">.properties</span></code>. For
example, if you name the property file <code class="docutils literal notranslate"><span class="pre">sales.properties</span></code>, Trino
creates a catalog named <code class="docutils literal notranslate"><span class="pre">sales</span></code> using the configured connector.</p>
</section>
<section id="general-configuration-properties">
<h3 id="general-configuration-properties">General configuration properties<a class="headerlink" href="#general-configuration-properties" title="Permalink to this heading">#</a></h3>
<p>The following table describes general catalog configuration properties for the
connector:</p>
<table>
<colgroup>
<col style="width: 30%"/>
<col style="width: 40%"/>
<col style="width: 30%"/>
</colgroup>
<thead>
<tr class="row-odd"><th class="head"><p>Property name</p></th>
<th class="head"><p>Description</p></th>
<th class="head"><p>Default value</p></th>
</tr>
</thead>
<tbody>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">case-insensitive-name-matching</span></code></p></td>
<td><p>Support case insensitive schema and table names.</p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">false</span></code></p></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">case-insensitive-name-matching.cache-ttl</span></code></p></td>
<td><p>This value should be a <a class="reference internal" href="../admin/properties.html#prop-type-duration"><span class="std std-ref">duration</span></a>.</p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">1m</span></code></p></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">case-insensitive-name-matching.config-file</span></code></p></td>
<td><p>Path to a name mapping configuration file in JSON format that allows
Trino to disambiguate between schemas and tables with similar names in
different cases.</p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">null</span></code></p></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">case-insensitive-name-matching.config-file.refresh-period</span></code></p></td>
<td><p>Frequency with which Trino checks the name matching configuration file
for changes. This value should be a <a class="reference internal" href="../admin/properties.html#prop-type-duration"><span class="std std-ref">duration</span></a>.</p></td>
<td><p>(refresh disabled)</p></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">metadata.cache-ttl</span></code></p></td>
<td><p><a class="reference internal" href="../admin/properties.html#prop-type-duration"><span class="std std-ref">The duration</span></a> for which metadata, including
table and column statistics, is cached.</p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">0s</span></code> (caching disabled)</p></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">metadata.cache-missing</span></code></p></td>
<td><p>Cache the fact that metadata, including table and column statistics, is
not available</p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">false</span></code></p></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">metadata.cache-maximum-size</span></code></p></td>
<td><p>Maximum number of objects stored in the metadata cache</p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">10000</span></code></p></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">write.batch-size</span></code></p></td>
<td><p>Maximum number of statements in a batched execution.
Do not change this setting from the default. Non-default values may
negatively impact performance.</p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">1000</span></code></p></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">dynamic-filtering.enabled</span></code></p></td>
<td><p>Push down dynamic filters into JDBC queries</p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">true</span></code></p></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">dynamic-filtering.wait-timeout</span></code></p></td>
<td><p>Maximum <a class="reference internal" href="../admin/properties.html#prop-type-duration"><span class="std std-ref">duration</span></a> for which Trino will wait for dynamic
filters to be collected from the build side of joins before starting a
JDBC query. Using a large timeout can potentially result in more detailed
dynamic filters. However, it can also increase latency for some queries.</p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">20s</span></code></p></td>
</tr>
</tbody>
</table>
</section>
<section id="appending-query-metadata">
<h3 id="appending-query-metadata">Appending query metadata<a class="headerlink" href="#appending-query-metadata" title="Permalink to this heading">#</a></h3>
<p>The optional parameter <code class="docutils literal notranslate"><span class="pre">query.comment-format</span></code> allows you to configure a SQL
comment that is sent to the datasource with each query. The format of this
comment can contain any characters and the following metadata:</p>
<ul class="simple">
<li><p><code class="docutils literal notranslate"><span class="pre">$QUERY_ID</span></code>: The identifier of the query.</p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">$USER</span></code>: The name of the user who submits the query to Trino.</p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">$SOURCE</span></code>: The identifier of the client tool used to submit the query, for
example <code class="docutils literal notranslate"><span class="pre">trino-cli</span></code>.</p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">$TRACE_TOKEN</span></code>: The trace token configured with the client tool.</p></li>
</ul>
<p>The comment can provide more context about the query. This additional
information is available in the logs of the datasource. To include environment
variables from the Trino cluster with the comment , use the
<code class="docutils literal notranslate"><span class="pre">${ENV:VARIABLE-NAME}</span></code> syntax.</p>
<p>The following example sets a simple comment that identifies each query sent by
Trino:</p>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>query.comment-format=Query sent by Trino.
</pre></div>
</div>
<p>With this configuration, a query such as <code class="docutils literal notranslate"><span class="pre">SELECT</span> <span class="pre">*</span> <span class="pre">FROM</span> <span class="pre">example_table;</span></code> is
sent to the datasource with the comment appended:</p>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>SELECT * FROM example_table; /*Query sent by Trino.*/
</pre></div>
</div>
<p>The following example improves on the preceding example by using metadata:</p>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>query.comment-format=Query $QUERY_ID sent by user $USER from Trino.
</pre></div>
</div>
<p>If <code class="docutils literal notranslate"><span class="pre">Jane</span></code> sent the query with the query identifier
<code class="docutils literal notranslate"><span class="pre">20230622_180528_00000_bkizg</span></code>, the following comment string is sent to the
datasource:</p>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>SELECT * FROM example_table; /*Query 20230622_180528_00000_bkizg sent by user Jane from Trino.*/
</pre></div>
</div>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>Certain JDBC driver settings and logging configurations might cause the
comment to be removed.</p>
</div>
</section>
<section id="domain-compaction-threshold">
<h3 id="domain-compaction-threshold">Domain compaction threshold<a class="headerlink" href="#domain-compaction-threshold" title="Permalink to this heading">#</a></h3>
<p>Pushing down a large list of predicates to the data source can compromise
performance. Trino compacts large predicates into a simpler range predicate
by default to ensure a balance between performance and predicate pushdown.
If necessary, the threshold for this compaction can be increased to improve
performance when the data source is capable of taking advantage of large
predicates. Increasing this threshold may improve pushdown of large
<a class="reference internal" href="../admin/dynamic-filtering.html"><span class="doc">dynamic filters</span></a>.
The <code class="docutils literal notranslate"><span class="pre">domain-compaction-threshold</span></code> catalog configuration property or the
<code class="docutils literal notranslate"><span class="pre">domain_compaction_threshold</span></code> <a class="reference internal" href="../sql/set-session.html#session-properties-definition"><span class="std std-ref">catalog session property</span></a> can be used to adjust the default value of
<code class="docutils literal notranslate"><span class="pre">32</span></code> for this threshold.</p>
</section>
<section id="procedures">
<h3 id="procedures">Procedures<a class="headerlink" href="#procedures" title="Permalink to this heading">#</a></h3>
<ul>
<li><p><code class="docutils literal notranslate"><span class="pre">system.flush_metadata_cache()</span></code></p>
<p>Flush JDBC metadata caches. For example, the following system call
flushes the metadata caches for all schemas in the <code class="docutils literal notranslate"><span class="pre">example</span></code> catalog</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="n">USE</span> <span class="n">example</span><span class="p">.</span><span class="n">example_schema</span><span class="p">;</span>
<span class="k">CALL</span> <span class="k">system</span><span class="p">.</span><span class="n">flush_metadata_cache</span><span class="p">();</span>
</pre></div>
</div>
</li>
</ul>
</section>
<section id="case-insensitive-matching">
<h3 id="case-insensitive-matching">Case insensitive matching<a class="headerlink" href="#case-insensitive-matching" title="Permalink to this heading">#</a></h3>
<p>When <code class="docutils literal notranslate"><span class="pre">case-insensitive-name-matching</span></code> is set to <code class="docutils literal notranslate"><span class="pre">true</span></code>, Trino
is able to query non-lowercase schemas and tables by maintaining a mapping of
the lowercase name to the actual name in the remote system. However, if two
schemas and/or tables have names that differ only in case (such as “customers”
and “Customers”) then Trino fails to query them due to ambiguity.</p>
<p>In these cases, use the <code class="docutils literal notranslate"><span class="pre">case-insensitive-name-matching.config-file</span></code> catalog
configuration property to specify a configuration file that maps these remote
schemas/tables to their respective Trino schemas/tables:</p>
<div class="highlight-json notranslate"><div class="highlight"><pre><span></span><span class="p">{</span>
  <span class="nt">"schemas"</span><span class="p">:</span> <span class="p">[</span>
    <span class="p">{</span>
      <span class="nt">"remoteSchema"</span><span class="p">:</span> <span class="s2">"CaseSensitiveName"</span><span class="p">,</span>
      <span class="nt">"mapping"</span><span class="p">:</span> <span class="s2">"case_insensitive_1"</span>
    <span class="p">},</span>
    <span class="p">{</span>
      <span class="nt">"remoteSchema"</span><span class="p">:</span> <span class="s2">"cASEsENSITIVEnAME"</span><span class="p">,</span>
      <span class="nt">"mapping"</span><span class="p">:</span> <span class="s2">"case_insensitive_2"</span>
    <span class="p">}],</span>
  <span class="nt">"tables"</span><span class="p">:</span> <span class="p">[</span>
    <span class="p">{</span>
      <span class="nt">"remoteSchema"</span><span class="p">:</span> <span class="s2">"CaseSensitiveName"</span><span class="p">,</span>
      <span class="nt">"remoteTable"</span><span class="p">:</span> <span class="s2">"tablex"</span><span class="p">,</span>
      <span class="nt">"mapping"</span><span class="p">:</span> <span class="s2">"table_1"</span>
    <span class="p">},</span>
    <span class="p">{</span>
      <span class="nt">"remoteSchema"</span><span class="p">:</span> <span class="s2">"CaseSensitiveName"</span><span class="p">,</span>
      <span class="nt">"remoteTable"</span><span class="p">:</span> <span class="s2">"TABLEX"</span><span class="p">,</span>
      <span class="nt">"mapping"</span><span class="p">:</span> <span class="s2">"table_2"</span>
    <span class="p">}]</span>
<span class="p">}</span>
</pre></div>
</div>
<p>Queries against one of the tables or schemes defined in the <code class="docutils literal notranslate"><span class="pre">mapping</span></code>
attributes are run against the corresponding remote entity. For example, a query
against tables in the <code class="docutils literal notranslate"><span class="pre">case_insensitive_1</span></code> schema is forwarded to the
CaseSensitiveName schema and a query against <code class="docutils literal notranslate"><span class="pre">case_insensitive_2</span></code> is forwarded
to the <code class="docutils literal notranslate"><span class="pre">cASEsENSITIVEnAME</span></code> schema.</p>
<p>At the table mapping level, a query on <code class="docutils literal notranslate"><span class="pre">case_insensitive_1.table_1</span></code> as
configured above is forwarded to <code class="docutils literal notranslate"><span class="pre">CaseSensitiveName.tablex</span></code>, and a query on
<code class="docutils literal notranslate"><span class="pre">case_insensitive_1.table_2</span></code> is forwarded to <code class="docutils literal notranslate"><span class="pre">CaseSensitiveName.TABLEX</span></code>.</p>
<p>By default, when a change is made to the mapping configuration file, Trino must
be restarted to load the changes. Optionally, you can set the
<code class="docutils literal notranslate"><span class="pre">case-insensitive-name-mapping.refresh-period</span></code> to have Trino refresh the
properties without requiring a restart:</p>
<div class="highlight-properties notranslate"><div class="highlight"><pre><span></span><span class="na">case-insensitive-name-mapping.refresh-period</span><span class="o">=</span><span class="s">30s</span>
</pre></div>
</div>
</section>
<section id="non-transactional-insert">
<h3 id="non-transactional-insert">Non-transactional INSERT<a class="headerlink" href="#non-transactional-insert" title="Permalink to this heading">#</a></h3>
<p>The connector supports adding rows using <a class="reference internal" href="../sql/insert.html"><span class="doc">INSERT statements</span></a>.
By default, data insertion is performed by writing data to a temporary table.
You can skip this step to improve performance and write directly to the target
table. Set the <code class="docutils literal notranslate"><span class="pre">insert.non-transactional-insert.enabled</span></code> catalog property
or the corresponding <code class="docutils literal notranslate"><span class="pre">non_transactional_insert</span></code> catalog session property to
<code class="docutils literal notranslate"><span class="pre">true</span></code>.</p>
<p>Note that with this property enabled, data can be corrupted in rare cases where
exceptions occur during the insert operation. With transactions disabled, no
rollback can be performed.</p>
</section>
</section>
<section id="type-mapping">
<span id="mysql-type-mapping"></span><h2 id="type-mapping">Type mapping<a class="headerlink" href="#type-mapping" title="Permalink to this heading">#</a></h2>
<p>Because Trino and MySQL each support types that the other does not, this
connector <a class="reference internal" href="../language/types.html#type-mapping-overview"><span class="std std-ref">modifies some types</span></a> when reading or
writing data. Data types may not map the same way in both directions between
Trino and the data source. Refer to the following sections for type mapping in
each direction.</p>
<section id="mysql-to-trino-type-mapping">
<h3 id="mysql-to-trino-type-mapping">MySQL to Trino type mapping<a class="headerlink" href="#mysql-to-trino-type-mapping" title="Permalink to this heading">#</a></h3>
<p>The connector maps MySQL types to the corresponding Trino types following
this table:</p>
<table id="id1">
<caption><span class="caption-text">MySQL to Trino type mapping</span><a class="headerlink" href="#id1" title="Permalink to this table">#</a></caption>
<colgroup>
<col style="width: 30%"/>
<col style="width: 20%"/>
<col style="width: 50%"/>
</colgroup>
<thead>
<tr class="row-odd"><th class="head"><p>MySQL database type</p></th>
<th class="head"><p>Trino type</p></th>
<th class="head"><p>Notes</p></th>
</tr>
</thead>
<tbody>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">BIT</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">BOOLEAN</span></code></p></td>
<td></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">BOOLEAN</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">TINYINT</span></code></p></td>
<td></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">TINYINT</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">TINYINT</span></code></p></td>
<td></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">TINYINT</span> <span class="pre">UNSIGNED</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">SMALLINT</span></code></p></td>
<td></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">SMALLINT</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">SMALLINT</span></code></p></td>
<td></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">SMALLINT</span> <span class="pre">UNSIGNED</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">INTEGER</span></code></p></td>
<td></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">INTEGER</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">INTEGER</span></code></p></td>
<td></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">INTEGER</span> <span class="pre">UNSIGNED</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">BIGINT</span></code></p></td>
<td></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">BIGINT</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">BIGINT</span></code></p></td>
<td></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">BIGINT</span> <span class="pre">UNSIGNED</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">DECIMAL(20,</span> <span class="pre">0)</span></code></p></td>
<td></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">DOUBLE</span> <span class="pre">PRECISION</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">DOUBLE</span></code></p></td>
<td></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">FLOAT</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">REAL</span></code></p></td>
<td></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">REAL</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">REAL</span></code></p></td>
<td></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">DECIMAL(p,</span> <span class="pre">s)</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">DECIMAL(p,</span> <span class="pre">s)</span></code></p></td>
<td><p>See <a class="reference internal" href="#mysql-decimal-handling"><span class="std std-ref">MySQL DECIMAL type handling</span></a></p></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">CHAR(n)</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">CHAR(n)</span></code></p></td>
<td></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">VARCHAR(n)</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">VARCHAR(n)</span></code></p></td>
<td></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">TINYTEXT</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">VARCHAR(255)</span></code></p></td>
<td></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">TEXT</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">VARCHAR(65535)</span></code></p></td>
<td></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">MEDIUMTEXT</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">VARCHAR(16777215)</span></code></p></td>
<td></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">LONGTEXT</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">VARCHAR</span></code></p></td>
<td></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">ENUM(n)</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">VARCHAR(n)</span></code></p></td>
<td></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">BINARY</span></code>, <code class="docutils literal notranslate"><span class="pre">VARBINARY</span></code>, <code class="docutils literal notranslate"><span class="pre">TINYBLOB</span></code>, <code class="docutils literal notranslate"><span class="pre">BLOB</span></code>, <code class="docutils literal notranslate"><span class="pre">MEDIUMBLOB</span></code>, <code class="docutils literal notranslate"><span class="pre">LONGBLOB</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">VARBINARY</span></code></p></td>
<td></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">JSON</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">JSON</span></code></p></td>
<td></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">DATE</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">DATE</span></code></p></td>
<td></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">TIME(n)</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">TIME(n)</span></code></p></td>
<td></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">DATETIME(n)</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">TIMESTAMP(n)</span></code></p></td>
<td></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">TIMESTAMP(n)</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">TIMESTAMP(n)</span> <span class="pre">WITH</span> <span class="pre">TIME</span> <span class="pre">ZONE</span></code></p></td>
<td></td>
</tr>
</tbody>
</table>
<p>No other types are supported.</p>
</section>
<section id="trino-to-mysql-type-mapping">
<h3 id="trino-to-mysql-type-mapping">Trino to MySQL type mapping<a class="headerlink" href="#trino-to-mysql-type-mapping" title="Permalink to this heading">#</a></h3>
<p>The connector maps Trino types to the corresponding MySQL types following
this table:</p>
<table id="id2">
<caption><span class="caption-text">Trino to MySQL type mapping</span><a class="headerlink" href="#id2" title="Permalink to this table">#</a></caption>
<colgroup>
<col style="width: 30%"/>
<col style="width: 20%"/>
<col style="width: 50%"/>
</colgroup>
<thead>
<tr class="row-odd"><th class="head"><p>Trino type</p></th>
<th class="head"><p>MySQL type</p></th>
<th class="head"><p>Notes</p></th>
</tr>
</thead>
<tbody>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">BOOLEAN</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">TINYINT</span></code></p></td>
<td></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">TINYINT</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">TINYINT</span></code></p></td>
<td></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">SMALLINT</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">SMALLINT</span></code></p></td>
<td></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">INTEGER</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">INTEGER</span></code></p></td>
<td></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">BIGINT</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">BIGINT</span></code></p></td>
<td></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">REAL</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">REAL</span></code></p></td>
<td></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">DOUBLE</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">DOUBLE</span> <span class="pre">PRECISION</span></code></p></td>
<td></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">DECIMAL(p,</span> <span class="pre">s)</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">DECIMAL(p,</span> <span class="pre">s)</span></code></p></td>
<td><p><a class="reference internal" href="#mysql-decimal-handling"><span class="std std-ref">MySQL DECIMAL type handling</span></a></p></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">CHAR(n)</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">CHAR(n)</span></code></p></td>
<td></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">VARCHAR(n)</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">VARCHAR(n)</span></code></p></td>
<td></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">JSON</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">JSON</span></code></p></td>
<td></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">DATE</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">DATE</span></code></p></td>
<td></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">TIME(n)</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">TIME(n)</span></code></p></td>
<td></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">TIMESTAMP(n)</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">DATETIME(n)</span></code></p></td>
<td></td>
</tr>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">TIMESTAMP(n)</span> <span class="pre">WITH</span> <span class="pre">TIME</span> <span class="pre">ZONE</span></code></p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">TIMESTAMP(n)</span></code></p></td>
<td></td>
</tr>
</tbody>
</table>
<p>No other types are supported.</p>
</section>
<section id="timestamp-type-handling">
<h3 id="timestamp-type-handling">Timestamp type handling<a class="headerlink" href="#timestamp-type-handling" title="Permalink to this heading">#</a></h3>
<p>MySQL <code class="docutils literal notranslate"><span class="pre">TIMESTAMP</span></code> types are mapped to Trino <code class="docutils literal notranslate"><span class="pre">TIMESTAMP</span> <span class="pre">WITH</span> <span class="pre">TIME</span> <span class="pre">ZONE</span></code>.
To preserve time instants, Trino sets the session time zone
of the MySQL connection to match the JVM time zone.
As a result, error messages similar to the following example occur when
a timezone from the JVM does not exist on the MySQL server:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="n">com</span><span class="p">.</span><span class="n">mysql</span><span class="p">.</span><span class="n">cj</span><span class="p">.</span><span class="n">exceptions</span><span class="p">.</span><span class="n">CJException</span><span class="p">:</span> <span class="k">Unknown</span> <span class="k">or</span> <span class="n">incorrect</span> <span class="n">time</span> <span class="k">zone</span><span class="p">:</span> <span class="s1">'UTC'</span>
</pre></div>
</div>
<p>To avoid the errors, you must use a time zone that is known on both systems,
or <a class="reference external" href="https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html#time-zone-installation">install the missing time zone on the MySQL server</a>.</p>
</section>
<section id="decimal-type-handling">
<span id="mysql-decimal-handling"></span><h3 id="decimal-type-handling">Decimal type handling<a class="headerlink" href="#decimal-type-handling" title="Permalink to this heading">#</a></h3>
<p><code class="docutils literal notranslate"><span class="pre">DECIMAL</span></code> types with unspecified precision or scale are mapped to a Trino
<code class="docutils literal notranslate"><span class="pre">DECIMAL</span></code> with a default precision of 38 and default scale of 0. The scale can
be changed by setting the <code class="docutils literal notranslate"><span class="pre">decimal-mapping</span></code> configuration property or the
<code class="docutils literal notranslate"><span class="pre">decimal_mapping</span></code> session property to <code class="docutils literal notranslate"><span class="pre">allow_overflow</span></code>. The scale of the
resulting type is controlled via the <code class="docutils literal notranslate"><span class="pre">decimal-default-scale</span></code> configuration
property or the <code class="docutils literal notranslate"><span class="pre">decimal-rounding-mode</span></code> session property. The precision is
always 38.</p>
<p>By default, values that require rounding or truncation to fit will cause a
failure at runtime. This behavior is controlled via the
<code class="docutils literal notranslate"><span class="pre">decimal-rounding-mode</span></code> configuration property or the
<code class="docutils literal notranslate"><span class="pre">decimal_rounding_mode</span></code> session property, which can be set to <code class="docutils literal notranslate"><span class="pre">UNNECESSARY</span></code>
(the default), <code class="docutils literal notranslate"><span class="pre">UP</span></code>, <code class="docutils literal notranslate"><span class="pre">DOWN</span></code>, <code class="docutils literal notranslate"><span class="pre">CEILING</span></code>, <code class="docutils literal notranslate"><span class="pre">FLOOR</span></code>, <code class="docutils literal notranslate"><span class="pre">HALF_UP</span></code>,
<code class="docutils literal notranslate"><span class="pre">HALF_DOWN</span></code>, or <code class="docutils literal notranslate"><span class="pre">HALF_EVEN</span></code> (see <a class="reference external" href="https://docs.oracle.com/en/java/javase/17/docs/api/java.base/java/math/RoundingMode.html#enum.constant.summary">RoundingMode</a>).</p>
</section>
<section id="type-mapping-configuration-properties">
<h3 id="type-mapping-configuration-properties">Type mapping configuration properties<a class="headerlink" href="#type-mapping-configuration-properties" title="Permalink to this heading">#</a></h3>
<p>The following properties can be used to configure how data types from the
connected data source are mapped to Trino data types and how the metadata is
cached in Trino.</p>
<table>
<colgroup>
<col style="width: 30%"/>
<col style="width: 40%"/>
<col style="width: 30%"/>
</colgroup>
<thead>
<tr class="row-odd"><th class="head"><p>Property name</p></th>
<th class="head"><p>Description</p></th>
<th class="head"><p>Default value</p></th>
</tr>
</thead>
<tbody>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">unsupported-type-handling</span></code></p></td>
<td><p>Configure how unsupported column data types are handled:</p>
<ul class="simple">
<li><p><code class="docutils literal notranslate"><span class="pre">IGNORE</span></code>, column is not accessible.</p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">CONVERT_TO_VARCHAR</span></code>, column is converted to unbounded <code class="docutils literal notranslate"><span class="pre">VARCHAR</span></code>.</p></li>
</ul>
<p>The respective catalog session property is <code class="docutils literal notranslate"><span class="pre">unsupported_type_handling</span></code>.</p>
</td>
<td><p><code class="docutils literal notranslate"><span class="pre">IGNORE</span></code></p></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">jdbc-types-mapped-to-varchar</span></code></p></td>
<td><p>Allow forced mapping of comma separated lists of data types to convert to
unbounded <code class="docutils literal notranslate"><span class="pre">VARCHAR</span></code></p></td>
<td></td>
</tr>
</tbody>
</table>
</section>
</section>
<section id="querying-mysql">
<h2 id="querying-mysql">Querying MySQL<a class="headerlink" href="#querying-mysql" title="Permalink to this heading">#</a></h2>
<p>The MySQL connector provides a schema for every MySQL <em>database</em>.
You can see the available MySQL databases by running <code class="docutils literal notranslate"><span class="pre">SHOW</span> <span class="pre">SCHEMAS</span></code>:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SHOW</span> <span class="n">SCHEMAS</span> <span class="k">FROM</span> <span class="n">example</span><span class="p">;</span>
</pre></div>
</div>
<p>If you have a MySQL database named <code class="docutils literal notranslate"><span class="pre">web</span></code>, you can view the tables
in this database by running <code class="docutils literal notranslate"><span class="pre">SHOW</span> <span class="pre">TABLES</span></code>:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SHOW</span> <span class="n">TABLES</span> <span class="k">FROM</span> <span class="n">example</span><span class="p">.</span><span class="n">web</span><span class="p">;</span>
</pre></div>
</div>
<p>You can see a list of the columns in the <code class="docutils literal notranslate"><span class="pre">clicks</span></code> table in the <code class="docutils literal notranslate"><span class="pre">web</span></code> database
using either of the following:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">DESCRIBE</span> <span class="n">example</span><span class="p">.</span><span class="n">web</span><span class="p">.</span><span class="n">clicks</span><span class="p">;</span>
<span class="k">SHOW</span> <span class="n">COLUMNS</span> <span class="k">FROM</span> <span class="n">example</span><span class="p">.</span><span class="n">web</span><span class="p">.</span><span class="n">clicks</span><span class="p">;</span>
</pre></div>
</div>
<p>Finally, you can access the <code class="docutils literal notranslate"><span class="pre">clicks</span></code> table in the <code class="docutils literal notranslate"><span class="pre">web</span></code> database:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">example</span><span class="p">.</span><span class="n">web</span><span class="p">.</span><span class="n">clicks</span><span class="p">;</span>
</pre></div>
</div>
<p>If you used a different name for your catalog properties file, use
that catalog name instead of <code class="docutils literal notranslate"><span class="pre">example</span></code> in the above examples.</p>
</section>
<section id="sql-support">
<span id="mysql-sql-support"></span><h2 id="sql-support">SQL support<a class="headerlink" href="#sql-support" title="Permalink to this heading">#</a></h2>
<p>The connector provides read access and write access to data and metadata in the
MySQL database. In addition to the <a class="reference internal" href="../language/sql-support.html#sql-globally-available"><span class="std std-ref">globally available</span></a> and
<a class="reference internal" href="../language/sql-support.html#sql-read-operations"><span class="std std-ref">read operation</span></a> statements, the connector supports
the following statements:</p>
<ul class="simple">
<li><p><a class="reference internal" href="../sql/insert.html"><span class="doc">INSERT</span></a></p></li>
<li><p><a class="reference internal" href="../sql/update.html"><span class="doc">UPDATE</span></a></p></li>
<li><p><a class="reference internal" href="../sql/delete.html"><span class="doc">DELETE</span></a></p></li>
<li><p><a class="reference internal" href="../sql/truncate.html"><span class="doc">TRUNCATE</span></a></p></li>
<li><p><a class="reference internal" href="../sql/create-table.html"><span class="doc">CREATE TABLE</span></a></p></li>
<li><p><a class="reference internal" href="../sql/create-table-as.html"><span class="doc">CREATE TABLE AS</span></a></p></li>
<li><p><a class="reference internal" href="../sql/drop-table.html"><span class="doc">DROP TABLE</span></a></p></li>
<li><p><a class="reference internal" href="../sql/create-schema.html"><span class="doc">CREATE SCHEMA</span></a></p></li>
<li><p><a class="reference internal" href="../sql/drop-schema.html"><span class="doc">DROP SCHEMA</span></a></p></li>
</ul>
<section id="update">
<h3 id="update">UPDATE<a class="headerlink" href="#update" title="Permalink to this heading">#</a></h3>
<p>Only <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code> statements with constant assignments and predicates are
supported. For example, the following statement is supported because the values
assigned are constants:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">UPDATE</span> <span class="k">table</span> <span class="k">SET</span> <span class="n">col1</span> <span class="o">=</span> <span class="mi">1</span> <span class="k">WHERE</span> <span class="n">col3</span> <span class="o">=</span> <span class="mi">1</span>
</pre></div>
</div>
<p>Arithmetic expressions, function calls, and other non-constant <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code>
statements are not supported. For example, the following statement is not
supported because arithmetic expressions cannot be used with the <code class="docutils literal notranslate"><span class="pre">SET</span></code>
command:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">UPDATE</span> <span class="k">table</span> <span class="k">SET</span> <span class="n">col1</span> <span class="o">=</span> <span class="n">col2</span> <span class="o">+</span> <span class="mi">2</span> <span class="k">WHERE</span> <span class="n">col3</span> <span class="o">=</span> <span class="mi">1</span>
</pre></div>
</div>
<p>The <code class="docutils literal notranslate"><span class="pre">=</span></code>, <code class="docutils literal notranslate"><span class="pre">!=</span></code>, <code class="docutils literal notranslate"><span class="pre">&gt;</span></code>, <code class="docutils literal notranslate"><span class="pre">&lt;</span></code>, <code class="docutils literal notranslate"><span class="pre">&gt;=</span></code>, <code class="docutils literal notranslate"><span class="pre">&lt;=</span></code>, <code class="docutils literal notranslate"><span class="pre">IN</span></code>, <code class="docutils literal notranslate"><span class="pre">NOT</span> <span class="pre">IN</span></code> operators are supported in
predicates. The following statement is not supported because the <code class="docutils literal notranslate"><span class="pre">AND</span></code> operator
cannot be used in predicates:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">UPDATE</span> <span class="k">table</span> <span class="k">SET</span> <span class="n">col1</span> <span class="o">=</span> <span class="mi">1</span> <span class="k">WHERE</span> <span class="n">col3</span> <span class="o">=</span> <span class="mi">1</span> <span class="k">AND</span> <span class="n">col2</span> <span class="o">=</span> <span class="mi">3</span>
</pre></div>
</div>
<p>All column values of a table row cannot be updated simultaneously. For a three
column table, the following statement is not supported:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">UPDATE</span> <span class="k">table</span> <span class="k">SET</span> <span class="n">col1</span> <span class="o">=</span> <span class="mi">1</span><span class="p">,</span> <span class="n">col2</span> <span class="o">=</span> <span class="mi">2</span><span class="p">,</span> <span class="n">col3</span> <span class="o">=</span> <span class="mi">3</span> <span class="k">WHERE</span> <span class="n">col3</span> <span class="o">=</span> <span class="mi">1</span>
</pre></div>
</div>
</section>
<section id="sql-delete">
<h3 id="sql-delete">SQL DELETE<a class="headerlink" href="#sql-delete" title="Permalink to this heading">#</a></h3>
<p>If a <code class="docutils literal notranslate"><span class="pre">WHERE</span></code> clause is specified, the <code class="docutils literal notranslate"><span class="pre">DELETE</span></code> operation only works if the
predicate in the clause can be fully pushed down to the data source.</p>
</section>
</section>
<section id="fault-tolerant-execution-support">
<span id="mysql-fte-support"></span><h2 id="fault-tolerant-execution-support">Fault-tolerant execution support<a class="headerlink" href="#fault-tolerant-execution-support" title="Permalink to this heading">#</a></h2>
<p>The connector supports <a class="reference internal" href="../admin/fault-tolerant-execution.html"><span class="doc">Fault-tolerant execution</span></a> of query
processing. Read and write operations are both supported with any retry policy.</p>
</section>
<section id="table-functions">
<h2 id="table-functions">Table functions<a class="headerlink" href="#table-functions" title="Permalink to this heading">#</a></h2>
<p>The connector provides specific <a class="reference internal" href="../functions/table.html"><span class="doc">table functions</span></a> to
access MySQL.</p>
<section id="query-varchar-table">
<span id="mysql-query-function"></span><h3 id="query-varchar-table"><code class="docutils literal notranslate"><span class="pre">query(varchar)</span> <span class="pre">-&gt;</span> <span class="pre">table</span></code><a class="headerlink" href="#query-varchar-table" title="Permalink to this heading">#</a></h3>
<p>The <code class="docutils literal notranslate"><span class="pre">query</span></code> function allows you to query the underlying database directly. It
requires syntax native to MySQL, because the full query is pushed down and
processed in MySQL. This can be useful for accessing native features which are
not available in Trino or for improving query performance in situations where
running a query natively may be faster.</p>
<p>The native query passed to the underlying data source is required to return a
table as a result set. Only the data source performs validation or security
checks for these queries using its own configuration. Trino does not perform
these tasks. Only use passthrough queries to read data.</p>
<p>For example, query the <code class="docutils literal notranslate"><span class="pre">example</span></code> catalog and group and concatenate all
employee IDs by manager ID:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span>
  <span class="o">*</span>
<span class="k">FROM</span>
  <span class="k">TABLE</span><span class="p">(</span>
    <span class="n">example</span><span class="p">.</span><span class="k">system</span><span class="p">.</span><span class="n">query</span><span class="p">(</span>
      <span class="n">query</span> <span class="o">=&gt;</span> <span class="s1">'SELECT</span>
<span class="s1">        manager_id, GROUP_CONCAT(employee_id)</span>
<span class="s1">      FROM</span>
<span class="s1">        company.employees</span>
<span class="s1">      GROUP BY</span>
<span class="s1">        manager_id'</span>
    <span class="p">)</span>
  <span class="p">);</span>
</pre></div>
</div>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>The query engine does not preserve the order of the results of this
function. If the passed query contains an <code class="docutils literal notranslate"><span class="pre">ORDER</span> <span class="pre">BY</span></code> clause, the
function result may not be ordered as expected.</p>
</div>
</section>
</section>
<section id="performance">
<h2 id="performance">Performance<a class="headerlink" href="#performance" title="Permalink to this heading">#</a></h2>
<p>The connector includes a number of performance improvements, detailed in the
following sections.</p>
<section id="table-statistics">
<span id="mysql-table-statistics"></span><h3 id="table-statistics">Table statistics<a class="headerlink" href="#table-statistics" title="Permalink to this heading">#</a></h3>
<p>The MySQL connector can use <a class="reference internal" href="../optimizer/statistics.html"><span class="doc">table and column statistics</span></a> for <a class="reference internal" href="../optimizer/cost-based-optimizations.html"><span class="doc">cost based optimizations</span></a>, to improve query processing performance
based on the actual data in the data source.</p>
<p>The statistics are collected by MySQL and retrieved by the connector.</p>
<p>The table-level statistics are based on MySQL’s <code class="docutils literal notranslate"><span class="pre">INFORMATION_SCHEMA.TABLES</span></code>
table. The column-level statistics are based on MySQL’s index statistics
<code class="docutils literal notranslate"><span class="pre">INFORMATION_SCHEMA.STATISTICS</span></code> table. The connector can return column-level
statistics only when the column is the first column in some index.</p>
<p>MySQL database can automatically update its table and index statistics. In some
cases, you may want to force statistics update, for example after creating new
index, or after changing data in the table. You can do that by executing the
following statement in MySQL Database.</p>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>ANALYZE TABLE table_name;
</pre></div>
</div>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>MySQL and Trino may use statistics information in different ways. For this
reason, the accuracy of table and column statistics returned by the MySQL
connector might be lower than than that of others connectors.</p>
</div>
<p><strong>Improving statistics accuracy</strong></p>
<p>You can improve statistics accuracy with histogram statistics (available since
MySQL 8.0). To create histogram statistics execute the following statement in
MySQL Database.</p>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>ANALYZE TABLE table_name UPDATE HISTOGRAM ON column_name1, column_name2, ...;
</pre></div>
</div>
<p>Refer to MySQL documentation for information about options, limitations
and additional considerations.</p>
</section>
<section id="pushdown">
<span id="mysql-pushdown"></span><h3 id="pushdown">Pushdown<a class="headerlink" href="#pushdown" title="Permalink to this heading">#</a></h3>
<p>The connector supports pushdown for a number of operations:</p>
<ul class="simple">
<li><p><a class="reference internal" href="../optimizer/pushdown.html#join-pushdown"><span class="std std-ref">Join pushdown</span></a></p></li>
<li><p><a class="reference internal" href="../optimizer/pushdown.html#limit-pushdown"><span class="std std-ref">Limit pushdown</span></a></p></li>
<li><p><a class="reference internal" href="../optimizer/pushdown.html#topn-pushdown"><span class="std std-ref">Top-N pushdown</span></a></p></li>
</ul>
<p><a class="reference internal" href="../optimizer/pushdown.html#aggregation-pushdown"><span class="std std-ref">Aggregate pushdown</span></a> for the following functions:</p>
<ul class="simple">
<li><p><a class="reference internal" href="../functions/aggregate.html#avg" title="avg"><code class="xref py py-func docutils literal notranslate"><span class="pre">avg()</span></code></a></p></li>
<li><p><a class="reference internal" href="../functions/aggregate.html#count" title="count"><code class="xref py py-func docutils literal notranslate"><span class="pre">count()</span></code></a></p></li>
<li><p><a class="reference internal" href="../functions/aggregate.html#max" title="max"><code class="xref py py-func docutils literal notranslate"><span class="pre">max()</span></code></a></p></li>
<li><p><a class="reference internal" href="../functions/aggregate.html#min" title="min"><code class="xref py py-func docutils literal notranslate"><span class="pre">min()</span></code></a></p></li>
<li><p><a class="reference internal" href="../functions/aggregate.html#sum" title="sum"><code class="xref py py-func docutils literal notranslate"><span class="pre">sum()</span></code></a></p></li>
<li><p><a class="reference internal" href="../functions/aggregate.html#stddev" title="stddev"><code class="xref py py-func docutils literal notranslate"><span class="pre">stddev()</span></code></a></p></li>
<li><p><a class="reference internal" href="../functions/aggregate.html#stddev_pop" title="stddev_pop"><code class="xref py py-func docutils literal notranslate"><span class="pre">stddev_pop()</span></code></a></p></li>
<li><p><a class="reference internal" href="../functions/aggregate.html#stddev_samp" title="stddev_samp"><code class="xref py py-func docutils literal notranslate"><span class="pre">stddev_samp()</span></code></a></p></li>
<li><p><a class="reference internal" href="../functions/aggregate.html#variance" title="variance"><code class="xref py py-func docutils literal notranslate"><span class="pre">variance()</span></code></a></p></li>
<li><p><a class="reference internal" href="../functions/aggregate.html#var_pop" title="var_pop"><code class="xref py py-func docutils literal notranslate"><span class="pre">var_pop()</span></code></a></p></li>
<li><p><a class="reference internal" href="../functions/aggregate.html#var_samp" title="var_samp"><code class="xref py py-func docutils literal notranslate"><span class="pre">var_samp()</span></code></a></p></li>
</ul>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>The connector performs pushdown where performance may be improved, but in
order to preserve correctness an operation may not be pushed down. When
pushdown of an operation may result in better performance but risks
correctness, the connector prioritizes correctness.</p>
</div>
<section id="cost-based-join-pushdown">
<h4 id="cost-based-join-pushdown">Cost-based join pushdown<a class="headerlink" href="#cost-based-join-pushdown" title="Permalink to this heading">#</a></h4>
<p>The connector supports cost-based <a class="reference internal" href="../optimizer/pushdown.html#join-pushdown"><span class="std std-ref">Join pushdown</span></a> to make intelligent
decisions about whether to push down a join operation to the data source.</p>
<p>When cost-based join pushdown is enabled, the connector only pushes down join
operations if the available <a class="reference internal" href="../optimizer/statistics.html"><span class="doc">Table statistics</span></a> suggest that doing so
improves performance. Note that if no table statistics are available, join
operation pushdown does not occur to avoid a potential decrease in query
performance.</p>
<p>The following table describes catalog configuration properties for
join pushdown:</p>
<table>
<colgroup>
<col style="width: 30%"/>
<col style="width: 40%"/>
<col style="width: 30%"/>
</colgroup>
<thead>
<tr class="row-odd"><th class="head"><p>Property name</p></th>
<th class="head"><p>Description</p></th>
<th class="head"><p>Default value</p></th>
</tr>
</thead>
<tbody>
<tr class="row-even"><td><p><code class="docutils literal notranslate"><span class="pre">join-pushdown.enabled</span></code></p></td>
<td><p>Enable <a class="reference internal" href="../optimizer/pushdown.html#join-pushdown"><span class="std std-ref">join pushdown</span></a>. Equivalent <a class="reference internal" href="../sql/set-session.html#session-properties-definition"><span class="std std-ref">catalog
session property</span></a> is
<code class="docutils literal notranslate"><span class="pre">join_pushdown_enabled</span></code>.</p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">true</span></code></p></td>
</tr>
<tr class="row-odd"><td><p><code class="docutils literal notranslate"><span class="pre">join-pushdown.strategy</span></code></p></td>
<td><p>Strategy used to evaluate whether join operations are pushed down. Set to
<code class="docutils literal notranslate"><span class="pre">AUTOMATIC</span></code> to enable cost-based join pushdown, or <code class="docutils literal notranslate"><span class="pre">EAGER</span></code> to
push down joins whenever possible. Note that <code class="docutils literal notranslate"><span class="pre">EAGER</span></code> can push down joins
even when table statistics are unavailable, which may result in degraded
query performance. Because of this, <code class="docutils literal notranslate"><span class="pre">EAGER</span></code> is only recommended for
testing and troubleshooting purposes.</p></td>
<td><p><code class="docutils literal notranslate"><span class="pre">AUTOMATIC</span></code></p></td>
</tr>
</tbody>
</table>
</section>
<section id="predicate-pushdown-support">
<h4 id="predicate-pushdown-support">Predicate pushdown support<a class="headerlink" href="#predicate-pushdown-support" title="Permalink to this heading">#</a></h4>
<p>The connector does not support pushdown of any predicates on columns with
<a class="reference internal" href="../language/types.html#string-data-types"><span class="std std-ref">textual types</span></a> like <code class="docutils literal notranslate"><span class="pre">CHAR</span></code> or <code class="docutils literal notranslate"><span class="pre">VARCHAR</span></code>.
This ensures correctness of results since the data source may compare strings
case-insensitively.</p>
<p>In the following example, the predicate is not pushed down for either query
since <code class="docutils literal notranslate"><span class="pre">name</span></code> is a column of type <code class="docutils literal notranslate"><span class="pre">VARCHAR</span></code>:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">nation</span> <span class="k">WHERE</span> <span class="n">name</span> <span class="o">&gt;</span> <span class="s1">'CANADA'</span><span class="p">;</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">nation</span> <span class="k">WHERE</span> <span class="n">name</span> <span class="o">=</span> <span class="s1">'CANADA'</span><span class="p">;</span>
</pre></div>
</div>
</section>
</section>
</section>
</section>


          </article>
        </div>
      </div>
    </main>
  </div>
  <footer class="md-footer">
    <div class="md-footer-nav">
      <nav class="md-footer-nav__inner md-grid">
          
            <a href="mongodb.html" title="MongoDB connector"
               class="md-flex md-footer-nav__link md-footer-nav__link--prev"
               rel="prev">
              <div class="md-flex__cell md-flex__cell--shrink">
                <i class="md-icon md-icon--arrow-back md-footer-nav__button"></i>
              </div>
              <div class="md-flex__cell md-flex__cell--stretch md-footer-nav__title">
                <span class="md-flex__ellipsis">
                  <span
                      class="md-footer-nav__direction"> Previous </span> MongoDB connector </span>
              </div>
            </a>
          
          
            <a href="oracle.html" title="Oracle connector"
               class="md-flex md-footer-nav__link md-footer-nav__link--next"
               rel="next">
            <div class="md-flex__cell md-flex__cell--stretch md-footer-nav__title"><span
                class="md-flex__ellipsis"> <span
                class="md-footer-nav__direction"> Next </span> Oracle connector </span>
            </div>
            <div class="md-flex__cell md-flex__cell--shrink"><i
                class="md-icon md-icon--arrow-forward md-footer-nav__button"></i>
            </div>
          
        </a>
        
      </nav>
    </div>
    <div class="md-footer-meta md-typeset">
      <div class="md-footer-meta__inner md-grid">
        <div class="md-footer-copyright">
        </div>
      </div>
    </div>
  </footer>
  <script src="../_static/javascripts/application.js"></script>
  <script>app.initialize({version: "1.0.4", url: {base: ".."}})</script>
  </body>
</html>