MySQL

This driver implements read and write access for spatial data in MySQL tables. This functionality was introduced in GDAL/OGR 1.3.2.

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

Caveats

Creation Issues

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.

Layer Creation Options

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.

MYSQL:westholland,user=root,password=psv9570,port=3306,tables=bedrijven
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.
ogr2ogr -f MySQL MySQL:test,user=root world_borders.shp -nln borders2 -update -overwrite -lco GEOMETRY_NAME=SHAPE2
The following example uses ogrinfo to return some summary information about the borders2 layer in the test database.
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)