septiembre 23, 2008 a las 10:20 am #152
José Navarro SánchezJefe de claves
Este texto es una traducción del libro MySQL 4th edition. Lo estoy traduciendo poco a poco.
22.214.171.124 Date and Time Data Type Attributes
The following remarks apply to all temporal types except TIMESTAMP:
* The general attributes NULL or NOT NULL may be specified. If you don’t specify either of them, NULL is the default.
You can also specify a default value using a DEFAULT clause. Section 3.2.3, “Specifying Column Default Values,” described the rules that MySQL uses for assigning a default value if a column definition includes no DEFAULT clause.
Note that because default values must be constants, you cannot use a function such as NOW() to supply a value of “the current date and time” as the default for a DATETIME column. To achieve that result, set the column value explicitly to NOW() whenever you create a new row, or else consider using a TIMESTAMP column instead. Alternatively, set up a trigger that initializes the column to the appropriate value; see Section 4.3, “Triggers.”
TIMESTAMP columns are special; the default for the first such column in a table is the current date and time, and the “zero” value for any others. However, the full set of rules governing default values is more complex. See Section 126.96.36.199, “The TIMESTAMP Data Type,” for details.
188.8.131.52 Working with Date and Time Values
MySQL tries to interpret input values for date and time columns in a variety of formats, including both string and numeric forms. Table 3.16 shows the allowable formats for each of the date and time types.
Table 3.16. Date and Time Type Input Formats
MySQL interprets formats that have no century part (CC) using the rules described in Section 184.108.40.206, “Interpretation of Ambiguous Year Values.” For string formats that include delimiter characters, you don’t have to use ‘-’ for dates and ‘:’ for times. Any punctuation character may be used as the delimiter. Interpretation of values depends on context, not on the delimiter. For example, although times are typically specified using a delimiter of ‘:’, MySQL won’t interpret a value containing ‘:’ as a time in a context where a date is expected. In addition, for the string formats that include delimiters, you need not specify two digits for month, day, hour, minute, or second values that are less than 10. The following are all equivalent:
MySQL may interpret values with leading zeros in different ways depending on whether they are specified as strings or numbers. The string ‘001231’ will be seen as a six-digit value and interpreted as ‘2000-12-31’ for a DATE, and as ‘2000-12-31 00:00:00’ for a DATETIME. On the other hand, the number 001231 will be seen as 1231 after the parser gets done with it and then the interpretation becomes problematic. This is a case where it’s best to supply a string value ‘001231’, or else use a fully qualified value if you are using numbers (that is, 20001231 for DATE and 200012310000 for DATETIME).
In general, you may freely assign values between the DATE, DATETIME, and TIMESTAMP types, although there are certain restrictions to keep in mind:
* If you assign a DATETIME or TIMESTAMP value to a DATE, the time part is discarded.
* If you assign a DATE value to a DATETIME or TIMESTAMP, the time part of the resulting value is set to zero (’00:00:00′).
* The types have different ranges. In particular, TIMESTAMP has a more limited range (1970 to 2038); so, for example, you cannot assign a pre-1970 DATETIME value to a TIMESTAMP and expect reasonable results. Nor can you assign values that are far in the future to a TIMESTAMP.
MySQL provides many functions for working with date and time values. See Appendix C for more information.
220.127.116.11 Interpretation of Ambiguous Year Values
For all date and time types that include a year part (DATE, DATETIME, TIMESTAMP, YEAR), MySQL handles values that contain two-digit years by converting them to four-digit years:
* Year values from 00 to 69 become 2000 to 2069
* Year values from 70 to 99 become 1970 to 1999
You can see the effect of these rules most easily by storing different two-digit values into a YEAR column and then retrieving the results. This also demonstrates something you should take note of:
mysql> CREATE TABLE y_table (y YEAR);
mysql> INSERT INTO y_table VALUES(68),(69),(99),(00),(’00’);
mysql> SELECT * FROM y_table;
| y |
| 2068 |
| 2069 |
| 1999 |
| 0000 |
| 2000 |
Observe that 00 is converted to 0000, not to 2000. That’s because, as a number, 00 is the same as 0, and is a perfectly legal value for the YEAR type. If you insert a numeric zero, that’s what you get. To get 2000 using a value that does not contain the century, insert the string ‘0’ or ’00’. You can make sure that MySQL sees a string and not a number by inserting YEAR values using CAST(value AS CHAR) to produce a string result uniformly regardless of whether value is a string or a number.
Keep in mind that the rules for converting two-digit to four-digit year values provide only a reasonable guess. There is no way for MySQL to be certain about the meaning of a two-digit year when the century is unspecified. MySQL’s conversion rules are adequate for many situations, but if they don’t produce the values that you want, it is necessary to provide unambiguous data with four-digit years. For example, to enter birth and death dates into the president table, which lists U.S. presidents back into the 1700s, four-digit year values are in order. Values in these columns span several centuries, so letting MySQL guess the century from a two-digit year is definitely the wrong thing to do.
3.2.7 Spatial Data Types
Spatial values enable representation of values such as points, lines, and polygons. These data types are implemented per the OpenGIS specification, which is available at the Open Geospatial Consortium Web site (http://www.opengeospatial.org/). Table 3.17 lists the spatial data types that MySQL supports.
Table 3.17. Spatial Data Types
A spatial value of any type
A point (a pair of X,Y coordinates)
A curve (one or more POINT values)
A collection of GEOMETRY values
A collection of LINESTRING values
A collection of POINT values
A collection of POLYGON values
The level of support for spatial types varies by storage engine. The most complete support is implemented in MyISAM. Other engines such as InnoDB, NDB, and ARCHIVE offer more limited support. For example, in MyISAM tables, spatial values can be indexed using either SPATIAL or non-SPATIAL indexes (using INDEX, UNIQUE, or PRIMARY KEY). Other engines that support spatial data types can use only non-SPATIAL indexes (except ARCHIVE, which cannot index spatial columns at all). If a table is partitioned, it cannot contain spatial columns.
Spatial columns included in a SPATIAL index cannot use NULL to represent missing values within columns, because SPATIAL indexes do not allow NULL values. Depending on your application, it might be acceptable to use an empty (zero-dimensional) value instead.
MySQL works with spatial values in three formats. One is the internal format that MySQL uses for storing spatial values in tables. The other two are the Well-Known Text (WKT) and Well-Known Binary (WKB) formats; these are standards for representing spatial values as text strings or in binary format. The syntax for text strings and the binary representation are defined in the OpenGIS specification. For example, the WKT format for a POINT value with coordinates of x and y is written as a string:
Note the absence of a comma between the coordinate values. In lists of multiple coordinates, commas separate pairs of x and y values. The following string represents a LINESTRING value consisting of several points:
‘LINESTRING(10 20, 0 0, 10 20, 0 0)’
More complex values have a more complex representation. This POLYGON has a rectangular outer boundary and a triangular inner boundary:
‘POLYGON((0 0, 100 0, 100 100, 0 100, 0 0),(30 30, 30 60, 45 60, 30 30))’
Because spatial values can be complex, most operations on them are done by invoking functions. The set of spatial functions is extensive and includes functions for converting from one format to another (see Appendix C).
The following example shows how to use several aspects of spatial support:
mysql> CREATE TABLE pt_tbl (p POINT);
mysql> INSERT INTO pt_tbl (p) VALUES
-> (POINTFROMTEXT(‘POINT(0 0)’)),
-> (POINTFROMTEXT(‘POINT(0 50)’)),
-> (POINTFROMTEXT(‘POINT(100 100)’));
mysql> CREATE FUNCTION dist (p1 POINT, p2 POINT)
-> RETURNS FLOAT DETERMINISTIC
-> RETURN SQRT(POW(X(p2)-X(p1),2) + POW(Y(p2)-Y(p1),2));
mysql> SET @ref_pt = POINTFROMTEXT(‘POINT(0 0)’);
mysql> SELECT ASTEXT(p), dist (p, @ref_pt) AS dist FROM pt_tbl;
| ASTEXT(p) | dist |
| POINT(0 0) | 0 |
| POINT(0 50) | 50 |
| POINT(100 100) | 141.42135620117 |
The example performs these operations:
1. It creates a table that includes a spatial column.
2. It populates the table with some POINT values, using the POINTFROMTEXT() function that produces an internal-format value from a WKT representation.
3. It creates a stored function that computes the distance between two points, using the X() and Y() functions to extract point coordinates.
4. It computes the distance of each point in the table from a given reference point.
Debes estar registrado para responder a este debate.