mysql姓氏排序规则,MySQL列全名时按姓氏排序

I have the following:

SELECT * FROM users LEFT JOIN user_info ON users.id=user_info.user_id

WHERE

((user_info.tester != 1) OR (user_info.tester is null)) AND

id in (SELECT explicituser_id FROM user_login WHERE (created < '2012-12-17' OR created >= date_add('2012-12-17', interval 1 day))) AND

id IN (SELECT participte_id FROM roster WHERE roster_id IN (6))

order by

substring_index(users.name, ' ', -1)

I'm simply trying to sort by the users' last name.

However, while it can sort by the first name, the last name is buggy. If the user has quotes around their name (ie. "Abigail Martinez" it will make the sorting incorrect. If the user provides only one name, and it's a nickname (ie. Juan), then it will also make it incorrect. And then there's middle initials (ie. Tiffany S Villa or Steve de la Makinov). Unfortunately, this uses only one column for the full name (users.name).

Any help is appreciated. Thanks!

解决方案

substring_index(TRIM(users.name), ' ', -1) Adding TRIM will remove trailing spaces. After that, sorting occurs as expected.