2,187
edits
Difference between revisions of "MySQL"
Jump to navigation
Jump to search
→Data Types: Added "Dates and Times"
(→SELECT Rows: Updated ORDER BY and JOIN) |
(→Data Types: Added "Dates and Times") |
||
| Line 90: | Line 90: | ||
* '''<code>VARCHAR</code>''' - Variable character length up a prescribed maximum (eg <code>VARCHAR(32)</code>). Max allowed is 65,535 (though in reality this max width is shared across all columns so is less in practice. | * '''<code>VARCHAR</code>''' - Variable character length up a prescribed maximum (eg <code>VARCHAR(32)</code>). Max allowed is 65,535 (though in reality this max width is shared across all columns so is less in practice. | ||
* '''<code>CHAR</code>''' - Fixed character length up a prescribed maximum (eg <code>CHAR(32)</code>). Max allowed is 255 | * '''<code>CHAR</code>''' - Fixed character length up a prescribed maximum (eg <code>CHAR(32)</code>). Max allowed is 255 | ||
=== Dates and Times === | |||
Date and time values need to specified in the general format of <code>YYYY-MM-DD HH:MM:SS</code> (date or time components should be omitted if required for the table column. MySQL is relaxed on the usage of delimiters, so <code>YYYY^MM^DD HH-MM-SS</code>, or <code>YYYYMMDDHHMMSS</code> should be fine so long as the overall order of year, month, day, etc is incorrect and the values are valid. | |||
{|class="vwikitable" | |||
! Type !! Min !! Max !! Comments | |||
|- | |||
| <code> DATE </code> || 1000-01-01 || 9999-12-31 | |||
|- | |||
| <code> TIME </code> || -838:59:59 || 838:59:59 | |||
|- | |||
| <code> TIMESTAMP </code> || 1970-01-01 00:00:01 || 2038-01-19 03:14:07 || Stored as UTC (converted to during INSERT and from during SELECT) | |||
|- | |||
| <code> DATETIME </code> || 1000-01-01 00:00:00 || 9999-12-31 23:59:59 | |||
|- | |||
| <code> YEAR </code> || 1901 || 2155 || | |||
|} | |||
When querying data for use in PHP scripts, DATETIME values need to be converted into Unix Timestamp, for example... | |||
<source lang="mysql">SELECT UNIX_TIMESTAMP(datetime) AS datetime FROM table;</source> | |||
=== NULL === | === NULL === | ||
| Line 102: | Line 122: | ||
<source lang="mysql"> | <source lang="mysql"> | ||
INSERT INTO ips SET ip=INET_ATON('10.1.2.3'); | INSERT INTO ips SET ip=INET_ATON('10.1.2.3'); | ||
SELECT INET_NTOA(ip) | SELECT INET_NTOA(ip) FROM ips; | ||
SELECT INET_NTOA(ip) | SELECT INET_NTOA(ip) FROM ips WHERE INET_NTOA(ip) LIKE '10.1.2.%'; | ||
</source> | </source> | ||
Alternatively, use <code>VARCHAR(15)</code> to store as text. | Alternatively, use <code>VARCHAR(15)</code> to store as text. | ||
| Line 109: | Line 129: | ||
The data returned by <code> INET_NTOA() </code> is in binary string format, which can occasionally cause problems. If you're passing the data into PowerShell, for example, you end up by a <code> [byte] </code> object that is nigh on impossible to convert to a standard string. To force MySQL to return a string wrap the command in <code>CONVERT(x, CHAR)</code>, eg | The data returned by <code> INET_NTOA() </code> is in binary string format, which can occasionally cause problems. If you're passing the data into PowerShell, for example, you end up by a <code> [byte] </code> object that is nigh on impossible to convert to a standard string. To force MySQL to return a string wrap the command in <code>CONVERT(x, CHAR)</code>, eg | ||
<source lang="mysql"> | <source lang="mysql"> | ||
SELECT CONVERT(INET_NTOA(ip), CHAR) AS ip | SELECT CONVERT(INET_NTOA(ip), CHAR) AS ip FROM ips; | ||
</source> | </source> | ||