I want to disable database prefix name while executing queries in MySQL.
I am using MySQl Workbench 6.3 CE.
For Example : SELECT * FROM test_db.test_table;
I want to remove test_db prefix.
Add a comment
|
3 Answers
You have to tell it what database to use .. So you do it the way you are describing. .. Or you tell MySQL which database to use BEFORE the SELECT IE
use test_db;
SELECT * FROM test_table;
6 Comments
Mohammed Abdullah
Yeah I know it, Is there any other option to disable permanently in UI or through script, as I am running java program to execute the scripts I can't execute the use query for every script.
Zak
Your question says
Workbench -- Java is a whole other thing .. You'll need to declare your database in your connection string .. Which you didn't post, or mention ..Mohammed Abdullah
Leave java, is there any other option to disable permanently without executing use query.
Zak
You are thinking about this wrong ... MySQL can have an infinite number of databases, so you need to
SELECT or USE your database at least initially to tell mySQL which database you are looking for ... There is no "default" schema for incoming queries .. It simply wouldn't know where to look! There is no "disabling" which database to use -- That's like having a address, but no street name ..Mohammed Abdullah
Yeah I know we can create infinite no of DBs. But is there any way by selecting particular database settings and disable prefix so that when I connect the particular database prefix not necessary to execute use query. For example : I found one option in CPanel Mysql admin by disabling DB prefix name
|
If you're using MySQL workbench, in the lefthand pane, there is a list of available databases. If you're only using one, you can right click on it and select "set as default schema", then any queries you run in that MySQL session will no longer need to be prefixed with the DB name. However, if you want to query another DB in the same session, you will have to append that DB's name as a prefix, or do the same process, and set that DB as the default schema.
See this link for pretty pictures and a full description: https://www.quackit.com/mysql/tutorial/mysql_default_database.cfm
They way MySQL works is that you either explicitly state which schema a specific DB object is in (e.g. a table) or it uses the current default schema. If you don't have a default schema set and don't use fully qualfied names, you will get an error from MySQL saying that you have to define a default schema first.
So what you want to accomplish is impossible. Either set a default schema (it's a per connection setting, so you can set it once and use with your other queries as long as you keep the connection open) or fully qualify your DB objects (which is the most flexible approach and also avoids certain ambiquities, like same named tables in different schemas).