mysql返回顺序,从MySQL返回结果时的数字顺序

I have the following types of titles in a table in my db:

Topic 1 blah blah

Topic 2 blah blah

Topic 3 blah blah

...

Topic 10 blah blah

Topic 11 blah blah

etc...

The select query will always return the results like this:

Topic 1 blah

Topic 10 blah blah

Topic 11 blah

...leaving out Topic 2, Topic 3 etc... until after all the teens will I get Topic 2 etc...

How can I get my results as:

Topic 1

Topic 2

Topic 3

.... all the way to Topic 9 and only then have Topic 10?

解决方案

This is a problem with your database design. The topic number should be stored as an integer. If you can't change the design, try this query instead:

SELECT title

FROM table1

ORDER BY CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(title, ' ', 2), ' ', -1)

AS UNSIGNED);

Result:

'topic 1 foo'

'topic 2 bar'

'topic 10 baz'

Test data:

DROP TABLE IF EXISTS table1;

CREATE TABLE table1 (title VARCHAR(100) NOT NULL);

INSERT INTO table1 (title) VALUES

('topic 1 foo'),

('topic 2 bar'),

('topic 10 baz');