Mysql: Difference between revisions
No edit summary |
|||
| (10 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
[[ | [[Category:Computer]] | ||
--------------------- | |||
* http://db4free.blogspot.com/2006/06/sorting-of-numeric-values-mixed-with.html | * http://db4free.blogspot.com/2006/06/sorting-of-numeric-values-mixed-with.html | ||
*variant | *variant | ||
| Line 5: | Line 6: | ||
order by cast(substr(album_name,1,2)) | order by cast(substr(album_name,1,2)) | ||
* the +0 way http://blog.feedmarker.com/2006/02/01/how-to-do-natural-alpha-numeric-sort-in-mysql/ | * the +0 way http://blog.feedmarker.com/2006/02/01/how-to-do-natural-alpha-numeric-sort-in-mysql/ | ||
** doesnt work | |||
==MySQL 5.7.5 - ONLY_FULL_GROUP_BY== | |||
http://stackoverflow.com/questions/23921117/disable-only-full-group-by | |||
* rewrite query with ANY_VALUE(column) | |||
* disable: | |||
<nowiki>SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));</nowiki> | |||
http://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html | |||
"In MySQL 5.7.5 and higher, ONLY_FULL_GROUP_BY also affects handling of queries that use DISTINCT and ORDER BY" | |||
===LOAD DATA=== | |||
LOAD DATA INFILE '[...]' IGNORE INTO TABLE `ti_ad2` FIELDS TERMINATED BY ';' ENCLOSED BY '\"' ESCAPED BY '\\' LINES TERMINATED BY '\n' | |||
LOAD DATA INFILE '[...]' IGNORE INTO TABLE `ti_ad2` FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES; | |||
Latest revision as of 2017-04-29T19:51:44
natural sort order by cast(substr(album_name,1,2))
- the +0 way http://blog.feedmarker.com/2006/02/01/how-to-do-natural-alpha-numeric-sort-in-mysql/
- doesnt work
MySQL 5.7.5 - ONLY_FULL_GROUP_BY
http://stackoverflow.com/questions/23921117/disable-only-full-group-by
- rewrite query with ANY_VALUE(column)
- disable:
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
http://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html "In MySQL 5.7.5 and higher, ONLY_FULL_GROUP_BY also affects handling of queries that use DISTINCT and ORDER BY"
LOAD DATA
LOAD DATA INFILE '[...]' IGNORE INTO TABLE `ti_ad2` FIELDS TERMINATED BY ';' ENCLOSED BY '\"' ESCAPED BY '\\' LINES TERMINATED BY '\n'
LOAD DATA INFILE '[...]' IGNORE INTO TABLE `ti_ad2` FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;