<html>
<head>
<title>MySQL</title>
</head>
<body bgcolor="#ffffff">
<h1>MySQL</h1>
This driver implements read and write access for spatial data in
<a href="http://www.mysql.org/">MySQL</a> tables. This functionality
was introduced in GDAL/OGR 1.3.2. <p>
When opening a database, it's name should be specified in the form
"MYSQL:dbname[,options]" where the options can include comma seperated
items like "user=*userid*", "password=*password*", "host=*host*" and
"port=*port*".<p>
As well, a "tables=*table*;*table*..." option can be
added to restrict access to a specific list of tables in the database. This
option is primarily useful when a database has a lot of tables, and scanning
all their schemas would take a significant amount of time. <p>
Currently all regular user tables are assumed to be layers from an OGR
point of view, with the table names as the layer names. Named views are
not currently supported.<p>
If a single integer field is a primary key, it will be used as the FID
otherwise the FID will be assigned sequentially, and fetches by FID will
be extremely slow. <p>
<p>By default, SQL statements are passed directly to the MySQL database engine.
It's also possible to request the driver to handle SQL commands
with <a href="/ogr/ogr_sql.html">OGR SQL</a> engine,
by passing <strong>"OGRSQL"</strong> string to the ExecuteSQL()
method, as name of the SQL dialect.</p>
<h2>Caveats</h2>
<ul>
<li>
In the case of a layer defined by a SQL statement, fields either named
"OGC_FID" or those that are defined as NOT NULL, are a PRIMARY KEY, and
are an integer-like field will be assumed to be the FID.
</li>
<li>
Geometry fields are read from MySQL using WKB format. Versions older
than 5.0.16 of MySQL are known to have issues with some WKB
generation and may not work properly.
</li>
<li>
The OGR_FID column, which can be overridden with the MYSQL_FID layer
creation option, is implemented as a
<b>INT UNIQUE NOT NULL AUTO_INCREMENT</b> field. This
appears to implicitly create an index on the field.
</li>
<li>
The geometry column, which defaults to <i>SHAPE</i> and can be overridden
with the GEOMETRY_NAME layer creation option, is created as a
<b>NOT NULL</b> column in unless SPATIAL_INDEX is disabled. By default
a spatial index is created at the point the table is created.
</li>
<li>
SRS information is stored using the OGC Simple Features for SQL layout, with
<i>geometry_columns</i> and <i>spatial_ref_sys</i> metadata tables being
created in the specified database if they do not already exist. The
<i>spatial_ref_sys</i> table is <b>not</b> pre-populated with SRS and
EPSG values like PostGIS. If no EPSG code is found for a given table,
the MAX(SRID) value will be used.
</li>
<li>
Connection timeouts to the server can be specified with the <b>MYSQL_TIMEOUT</b>
environment variable. For example, SET MYSQL_TIMEOUT=3600. It is possible this
variable only has an impact when the OS of the MySQL server is Windows.
</li>
<li>
The MySQL driver opens a connection to the database using CLIENT_INTERACTIVE mode.
You can adjust this setting (interactive_timeout) in your mysql.ini or mysql.cnf
file of your server to your liking.
</li>
<li>
We are using WKT to insert geometries into the database.
If you are inserting big geometries, you will need to be aware of the <i>max_allowed_packet</i>
parameter in the MySQL configuration. By default it is set to 1M, but this will not
be large enough for really big geometries. If you get an error message like:
<i>Got a packet bigger than 'max_allowed_packet' bytes</i>, you will need to increase
this parameter.
</li>
</ul>
<h2>Creation Issues</h2>
The MySQL driver does not support creation of new datasets (a database
within MySQL), but it does allow creation of new layers within an
existing database.<P>
By default, the MySQL driver will attempt to preserve the precision
of OGR features when creating and reading MySQL layers. For integer fields
with a specified width, it will use <b>DECIMAL</b> as the MySQL field
type with a specified precision of 0. For real fields, it will use
<b>DOUBLE</b> with the specified width and precision. For string fields
with a specified width, <b>VARCHAR</b> will be used.<p>
The MySQL driver makes no allowances for character encodings at this time.<p>
The MySQL driver is not transactional at this time.<p>
<h3>Layer Creation Options</h3>
<ul>
<li>
<b>OVERWRITE</b>: This may be "YES" to force an existing layer of the
desired name to be destroyed before creating the requested layer.
</li>
<li>
<b>LAUNDER</b>: This may be "YES" to force new fields created on this
layer to have their field names "laundered" into a form more
compatible with MySQL. This converts to lower case and converts
some special characters like "-" and "#" to "_". If "NO" exact names
are preserved. The default value is "YES".
</li>
<li>
<b>PRECISION</b>: This may be "TRUE" to attempt to preserve field
widths and precisions for the creation and reading of MySQL layers.
The default value is "TRUE".
</li>
<li>
<b>MYSQL_GEOM_COLUMN</b>: This option specifies the name of the
geometry column. The default value is "SHAPE".
</li>
<li>
<b>MYSQL_FID</b>: This option specifies the name of the FID column.
The default value is "OGR_FID"
</li>
<li>
<b>SPATIAL_INDEX</b>: May be "NO" to stop automatic creation of
a spatial index on the geometry column, allowing NULL geometries
and possibly faster loading.
</li>
<li>
<b>ENGINE</b>: Optionally specify database engine to use. In MySQL
4.x this must be set to MyISAM for spatial tables.
</li>
</ul>
The following example datasource name opens the database schema
<i>westholland</i> with password <i>psv9570</i> for userid <i>root</i>
on the port <i>3306</i>. No hostname is provided, so localhost is assumed.
The tables= directive means that only the bedrijven table is scanned and
presented as a layer for use.<p>
<pre>
MYSQL:westholland,user=root,password=psv9570,port=3306,tables=bedrijven
</pre>
The following example uses ogr2ogr to create copy the world_borders layer
from a shapefile into a MySQL table. It overwrites a table with the existing
name <i>borders2</i>, sets a layer creation option to specify the geometry
column name to <i>SHAPE2</i>.
<pre>
ogr2ogr -f MySQL MySQL:test,user=root world_borders.shp -nln borders2 -update -overwrite -lco GEOMETRY_NAME=SHAPE2
</pre>
The following example uses ogrinfo to return some summary information about the borders2
layer in the test database.
<pre>
ogrinfo MySQL:test,user=root borders2 -so
Layer name: borders2
Geometry: Polygon
Feature Count: 3784
Extent: (-180.000000, -90.000000) - (180.000000, 83.623596)
Layer SRS WKT:
GEOGCS["GCS_WGS_1984",
DATUM["WGS_1984",
SPHEROID["WGS_84",6378137,298.257223563]],
PRIMEM["Greenwich",0],
UNIT["Degree",0.017453292519943295]]
FID Column = OGR_FID
Geometry Column = SHAPE2
cat: Real (0.0)
fips_cntry: String (80.0)
cntry_name: String (80.0)
area: Real (15.2)
pop_cntry: Real (15.2)
</body>
</html>
