What is the MySQL equivalent of a PostgreSQL 'schema'?

views:

answers:

+1 Q:

What is the MySQL equivalent of a PostgreSQL 'schema'?

I have a PostgreSQL database whose tables are divided amongst a number of schemas. Each schema has a different set of access controls; for example, one schema might be read-only to regular users, while they are allowed to create tables on another. Schemas also act as namespaces, so users don't have to worry about duplicating existing tables when they create new ones.

I want to create a similar setup using MySQL. Does it have an equivalent concept? If not, how can I most closely simulate it? I would prefer not to use multiple databases.

Prefixing table names is what's done with most MySQL-driven apps.

orlandu63 2009-12-18 02:12:02

It's not clear why this was downvoted; given that my question said that multiple databases were not desirable, this answer is not unreasonable.

DNS 2009-12-18 14:51:26

I'm not the downvoter, but a guess: prefixing table name is in no way the same as schemas. There is no way to set permissions, you are excluding certain table names, quoting rules are *completely* different, there is no way to set a seach patch, etc, etc, etc. Using a different database at least covers a few of them, but in the end you just cannot simulate it in a system that doesn't have it.

Magnus Hagander 2009-12-19 12:54:05

+3 A:

Database should be the closest one.

Milen A. Radev 2009-12-18 02:12:56

That's correct, but they have disadvantages. This database is intended for deployment onto various existing clusters. Using multiple databases makes that more complicated, since the user running the deployment can't just ask their admin to make a database; they need CREATE DATABASE privileges themselves. Also, if I want to preserve my current schema names, I risk colliding with existing databases on that cluster. Hence my preference for avoiding multiple databases if at all possible.

DNS 2009-12-18 02:27:58

That's why I said it's the closest, not equivalent.

Milen A. Radev 2009-12-18 10:33:33

ansaurus

tags:

views:

answers:

What is the MySQL equivalent of a PostgreSQL 'schema'?

related questions