CODEWITHSUNDEEP
mysql
bvnbhati
bvnbhati

Reputation: 380

MySQL -parameterized queries

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.

Upvotes: 0

Views: 85

Answers (1)

overflowed
overflowed

Reputation: 1838

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;

Upvotes: 1

Related Questions