EVOLUTION-MANAGER

Edit File: drv_mysql.html

<html>
<head>
<title>MySQL</title>
</head>

<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.

When opening a database, it's name should be specified in the form
"MYSQL:dbname[,options]" where the options can include comma separated
items like "user=*userid*", "password=*password*", "host=*host*" and
"port=*port*".

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.

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.

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.

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_sql.html">OGR SQL</a> engine,
by passing "OGRSQL" string to the ExecuteSQL()
method, as name of the SQL dialect.

<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
 INT UNIQUE NOT NULL AUTO_INCREMENT field. This
 appears to implicitly create an index on the field.
 </li>
 <li>
 The geometry column, which defaults to SHAPE and can be overridden
 with the GEOMETRY_NAME layer creation option, is created as a
 NOT NULL 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
 geometry_columns and spatial_ref_sys metadata tables being
 created in the specified database if they do not already exist. The
 spatial_ref_sys table is not 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 MYSQL_TIMEOUT
	 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 max_allowed_packet
	 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:
	 Got a packet bigger than 'max_allowed_packet' bytes, 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.

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 DECIMAL as the MySQL field
type with a specified precision of 0. For real fields, it will use
DOUBLE with the specified width and precision. For string fields
with a specified width, VARCHAR will be used.

The MySQL driver makes no allowances for character encodings at this time.

The MySQL driver is not transactional at this time.

<h3>Layer Creation Options</h3>

<ul>
 <li>
 OVERWRITE: This may be "YES" to force an existing layer of the
 desired name to be destroyed before creating the requested layer.
 </li>
 <li>
 LAUNDER: 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>
 PRECISION: 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>
 GEOMETRY_NAME: This option specifies the name of the
 geometry column. The default value is "SHAPE".
 </li>
 <li>
 FID: This option specifies the name of the FID column.
 The default value is "OGR_FID". Note: option was called MYSQL_FID in releases before GDAL 2
 </li>
 <li>
 FID64: (GDAL &gt;= 2.0) This may be "TRUE" to create a FID column that can support
 64 bit identifiers. The default value is "FALSE".
 </li>
 <li>
 SPATIAL_INDEX: May be "NO" to stop automatic creation of
 a spatial index on the geometry column, allowing NULL geometries
 and possibly faster loading.
 </li>
 <li>
 ENGINE: 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
westholland with password psv9570 for userid root
on the port 3306. 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.

<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 borders2, sets a layer creation option to specify the geometry
column name to SHAPE2.

<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>