tags:

views:

136

answers:

3

Does vanilla MySQL have a table name length limit?

(I've tested to 100 chrs on my modified MySQL - no limit so far.. I don't have vanilla MySQL accessible readily, though, but I wonder if my dynamically-created table schema will run on default MySQL)

  • Question: What happens if you go over 64 chrs on default MySQL? Can you test this please and paste MySQL error or results if any?

  • n.b. usage scenario where per user would never select another user's dataset.

+3  A: 

Here're the limits

Database: 64 
Table: 64 
Column: 64 
Index: 64 
Constraint: 64 
Stored Function or Procedure: 64 
Trigger: 64 
View: 64 
Compound Statement Label: 16

http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

hgulyan
any chance you can test this? and what happens if you go over 64?
ina
Same thing that happens if you turn the volume up past 11
Michael Robinson
You wanted limits, here they are, it's not a discussion.
hgulyan
Who downvoted my answer? Specify, the reason, if you do so.
hgulyan
+1  A: 

I think it's 64 characters. At least that's the limit on my setup.

Attempted with 133 characters:

mysql> create table aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa ( id INT );
ERROR 1103 (42000): Incorrect table name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
Michael Robinson
+1  A: 

The question is based on wrong assumptions.
There shouldn't be a direct answer.
It is duty of every fellow SOer to warn the OP against wrong decision. Instead of helping him wrong way to get your rep points.

Always avoid dynamically-created table schemas. Database schema is not the thing that should be dynamic. The data in the tables - yes. but not tables itself

Note that you are using a relational database. And while it is as simple as an egg to make a relation based on the field value, at the same time it's impossible based on table names.
Therefore, there shouldn't be dynamically created tables and data splitting. Use one table for similar data. That's one of most basic database rules.

Col. Shrapnel
OP accept this answer! @Col, I thought it was our duty to push jQuery as the solution for all of life's problems?
Michael Robinson
@Michael don't be so sarcastic. Any answer that is not direct and positive, will be most likely downvoted, as "not a real answer" (no matter if the question was "real" or not). I's very odd local habit. So, I have to put this disclaimer.
Col. Shrapnel
Didn't know that, thanks for the information. I'll try to be less sarcastic in the future.
Michael Robinson
@Col in that case - when do you use dynamically created table schemata? and can you provide something other than dogma on why *not* to use dynamically created table schemata?
ina
@Michael i don't believe accepting an answer should be a popularity contest, and i don't consider a question answered until it's answered. (as a result, @col's become my stalker.) also, unlike @col, i tend to be at least diplomatic in my replies even to questions i may find outrageous. anyway.
ina
@ina I even repeat it twice. May I ask you a question? Did you happen to read a book on database basics or take some classes on it?
Col. Shrapnel