Mysql: Difference between revisions

Latest revision as of 2017-04-29T19:51:44

natural sort
order by cast(substr(album_name,1,2))

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 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;

@@ Line 7: / Line 7: @@
 * 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;