📄 drv_mysql.html
字号:
<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 seperateditems like "user=*userid*", "password=*password*", "host=*host*" and "port=*port*".<p>As well, a "tables=*table*;*table*..." option can beadded to restrict access to a specific list of tables in the database. Thisoption is primarily useful when a database has a lot of tables, and scanningall their schemas would take a significant amount of time. <p>Currently all regular user tables are assumed to be layers from an OGRpoint of view, with the table names as the layer names. Named views arenot 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 willbe extremely slow. <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 databasewithin MySQL), but it does allow creation of new layers within anexisting 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 andpresented 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>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -