MySQL -parameterized queries

Question

I have designed a database where I have few global tables shared by multiple common template tables.

The idea is for each customer of my company, I have a set of customer specific tables. These tables have same structure and have foreign keys for few global tables.

Customer specific tables have names like table for e.g. table1 and table2.

Now I want to write queries similar to those allowed in Sybase as show below.

parameter CUST;
select * from table{CUST};
output to table{CUST}.txt;

above query will be called interactively as

isql a.sql 1
isql a.sql 2

Please help.

EDIT Once I follow up your suggestions, I will end up getting 2 column primary key for all tables where I have one column primary key, then no auto-increment option for these keys. I know you would suggest a false auto-increment column whose limits will then have no bounds theoretically.

Also, right now we have about 50,000 to few 100k transaction per client per year and transaction IDs are manageable in few digits, when need to be included in business documents. Once having global tables either transaction ids will be too long or they will all have client ID as prefix. Too much to manage. Another major issue would be row level security checks/locks would be needed to ensure every client has access to only its own records. Every filter will be changed and so on list is too long.

I don't think that's the way to go with mysql, are you sure you need one table per customer? — Sebi
– Sebi, Commented Dec 29, 2014 at 13:13
You should think about your schema and avoid duplicate content. Maybe a simple user-coloum may work for you. (I don`t fully get what you want to store) — bish
– bish, Commented Dec 29, 2014 at 13:14
@bish I can't change my design decision. Actually, I have large dataset in each specific table. There are so many specific tables for each customer. Also, global tables have to be global- reason they include common items like zip codes for addresses, various codes defined by business protocol etc. Also many times processes will run just for 1 customer and even for performance reasons customer specific tables need to be isolated. — bvnbhati
– bvnbhati, Commented Dec 29, 2014 at 14:23
As the local said. "If you want to go there I would not start here". CHANGE your schema before you go down a dead end. — david strachan
– david strachan, Commented Dec 29, 2014 at 16:45

overflowed · Accepted Answer · 2014-12-29 13:20:21Z

1

You can't parametrize table names. use one table for all customers with a customer column instead and use the respective wheres in your queries.

e.g.

select * form table where customer = @cust;

answered Dec 29, 2014 at 13:20

overflowed

1,83812 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bvnbhati Over a year ago

This is not feasible. I have large dataset in each specific table. There are so many specific tables for each customer. Also, global tables have to be global- reason they include common items like zip codes for addresses, various codes defined by business protocol etc.

Collectives™ on Stack Overflow

MySQL -parameterized queries

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related