Xaprb

How to change the default database in MySQL

with 10 comments

10 Responses to 'How to change the default database in MySQL'

Subscribe to comments with RSS or TrackBack to 'How to change the default database in MySQL'.

1. The only way I know of without reconnecting is to create a new database, USE it, then drop it. Now you have no default database.

   

   Brian Papantonio

   11 Oct 08 at 11:12 pm
2. I’ve just looked around digitally and in DuBois and I don’t think you can.

   

   Don McArthur

   11 Oct 08 at 11:28 pm
3. [...] http://www.xaprb.com/blog/2008/10/11/how-to-change-the-default-database-in-mysql/ asks Hoosgot, [...]

   

   How to change the default database in MySQL …

   11 Oct 08 at 11:37 pm
4. Ya creating, selecting and then dropping a db is one way, and reconnecting (or change user through the client API) is another.
   That might be it.

   So, what do you want to do that requires this?

   

   Arjen Lentz

   11 Oct 08 at 11:49 pm
5. Ah, it’s just a thing — sometimes I want to test things out without a default DB. For example, to make sure all table names in the query are fully qualified so there’s no ambiguity. Or if I’m working on some bug or other, or reproducing behavior on a client’s server. And I’m lazy, and I don’t want to disconnect and reconnect.

   

   Xaprb

   12 Oct 08 at 12:34 am
6. MySQL test suite sometimes uses approach described by Brian. In other cases it uses special option for mysqltest’s “connect” command (indeed, it is probably not an approach that can be used in your case).

   

   Dmitry Lenev

   12 Oct 08 at 2:10 am
7. I just tested select_db() with an empty DB name, which issues a COM_DB_INIT at the protocol layer, and it doesn’t work. It sends the “correct” packet, with a 0 length DB, but you get back an error: “No database selected”. I suppose a bug/feature request could be filed to change the behavior of this request. :)

   

   Eric Day

   12 Oct 08 at 2:54 am
8. \r

   

   Mikael Fridh

   12 Oct 08 at 4:42 am
9. >> all table names in the query are fully qualified so there’s no ambiguity
   If that is all you are looking for, then “explain extended” and then show warnings will let you know the ‘database.table’ used by MySQL

   

   Shantanu Oak

   12 Oct 08 at 6:43 am
10. Shantanu, that’s a trick I also use sometimes to help me figure out which table a column comes from. Developers think it’s obvious, but when you’re a consultant it’s tough to optimize queries when you don’t know the schema by heart!

    

    Xaprb

    12 Oct 08 at 8:43 am

Leave a Reply

Name (required)

Mail (will not be published) (required)

Website

Type the word 'xaprb'.

Notify me of followup comments via e-mail