mysql自身连接取两个别名,PHP / MySQL：在没有别名的连接查询中获取具有相同名称的多个列？...

I have two tables. One for users and one for posts.

The users table has the following fields:

id, username, password, created_at, modified_at

The posts table has the following fields:

id, user_id, title, body, created_at, modified_at

When I use a query like:

SELECT * FROM `users` LEFT OUTER JOIN `posts` ON users.id=posts.user_id

And fetch the results using PDO:

$sth = $this->$default_connection->prepare($query);

$sth->execute();

$sth->fetchAll(PDO::FETCH_ASSOC);

The returned array overwrites all the columns with the same names like id, created_at, modified_at like this:

Array

(

[0] => Array

(

[id] => 1

[username] => johnSmith

[password] => 2348r7edf8s79aa0230

[created_at] => 0000-00-00 00:00:00

[modified_at] => 0000-00-00 00:00:00

[user_id] => 18

[title] => First Post

[body] => Just testing...

)

)

The id field for the user is not shown, instead overwritten by the id of the post. Same goes for the created_at & modified_at fields.

I believe I can solve this problem by either using aliases or something like this:

SELECT

users.id,

users.username,

users.password,

users.created_at,

users.modified_at,

posts.id AS postId,

posts.user_id,

posts.title,

posts.created_at AS postCreatedAt,

posts.modified_at AS postModifiedAt

FROM `users`

LEFT OUTER JOIN `posts` ON (users.id=postId)

Or something similar, but I was wondering if there was a way to do this without manually writing out the name of every single column or an alias for every time I want to distinguish between two columns of the same name?

Is it possible to do something like:

SELECT users.* AS User and posts.* AS Post from `users` LEFT OUTER JOIN ON (User.id=Post.user_id)

And have it automatically use aliases for every column?

Or is there any other faster/more convenient way to do this?

Or can I do this without having to define any aliases myself?

Thank you

解决方案

I think alias is perfect in this case

SELECT table1.column AS column1, table2.column AS column2

FROM table1

LEFT JOIN table2

ON table1.column = table2.column

To save you some typing time, use table aliases:

SELECT t1.column AS column1, t2.column AS column2

FROM table1 AS t1

LEFT JOIN table2 AS t2

ON t1.column = t2.column