7

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.

philipxy
  • 14,867
  • 6
  • 39
  • 83
ina
  • 19,167
  • 39
  • 122
  • 201
  • 7
    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 – Your Common Sense Aug 15 '10 at 09:26
  • usage scenario: each user can potentially have hundreds of thousands of entries. rather than dumping everything in one db, i've found that `table_username_taskname` (where latter 2 are variables) makes the data more manageable. i am not sure how else to handle such a scenario. – ina Aug 15 '10 at 09:48
  • Tried it with 133 characters, see my updated answer. – Michael Robinson Aug 15 '10 at 09:57
  • 5
    nope. it does make data LESS manageable. The latter 2 should be a fields, not silly tablename variables. There are tables of hyndreds of millions of entries and nothing bad with them. You desperately need to buy yourself a book on database basics. – Your Common Sense Aug 15 '10 at 09:58
  • Once, at like some camp or something I had a table with 8M+ rows. Queries phrased well went through FAST. – Michael Robinson Aug 15 '10 at 10:00
  • 1
    Note that you are using a **relational** database. And while it is as simple as an egg to make relation based on the field values, at the same time it's impossible based on table names. – Your Common Sense Aug 15 '10 at 10:05
  • it just seems like the other two fields are vestigial. usage scenario where per user would never select another user's dataset for example. – ina Aug 15 '10 at 22:42
  • Possible duplicate of [Maximum length of a table name in MySQL](https://stackoverflow.com/questions/6868302/maximum-length-of-a-table-name-in-mysql) – pringi Nov 13 '17 at 10:24
  • i asked this in 2010 while the duplicate is from 2011 - why is my question considered a duplicate of a future question and not the other way around? – ina Nov 15 '17 at 10:09

4 Answers4

31

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
  • 8,099
  • 8
  • 50
  • 75
7

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.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • 1
    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 Aug 15 '10 at 10:14
  • @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. – Your Common Sense Aug 15 '10 at 10:21
  • Didn't know that, thanks for the information. I'll try to be less sarcastic in the future. – Michael Robinson Aug 15 '10 at 10:27
  • @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 Aug 15 '10 at 22:45
  • @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 Aug 15 '10 at 22:50
  • @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? – Your Common Sense Aug 16 '10 at 04:37
  • I too was once looking at creating a DB system with a dynamic schema (as a student, mind you), and I have to say if you're seriously considering it, you need to step back and re-think your data structure. For me this took some time researching normalization, and I came to the realization that with enough normalization (aka, 3NF), no dynamic schema is ever required. I'd highly suggest reading the wikipedia articles on this subject or some of the great quesitons here: http://stackoverflow.com/questions/2331838/normalization-in-plain-english – Mikey P Aug 22 '11 at 07:36
  • 2
    @YourCommonSense, from an academic standpoint, I would agree with you. but in a real-world please-the-customer scenario, sometimes it's the most logical approach. I would say you don't know enough about OP's scenario to say definitively that this is an incorrect approach. Also, you have not even attempted to answer the question, just sort of chided the OP for making "wrong decision." – Chris Drappier May 06 '13 at 17:50
  • 1
    @YourCommonSense, From what I know the OP should research the reason they want to do something outside of norm. However, just because its outside of mine and your norm's does not make it wrong. Words like ALWAYS are bad for creativity and advancements. Many of modern-day norms are one point were considered taboo and should NEVER do. I have even seen some that fade in and out of ALWAYS and NEVER. You really should take your own advice, and read some books on how CS has advanced. Now if you had answered the question, and advised against it, that would be understandable. – Jdahern Apr 24 '15 at 22:06
0

For me, it failed at 54 characters. I am using MySQL behind Django 1.4.5.

Server version: 5.5.40-0ubuntu0.14.04.1 (Ubuntu)
Umair A.
  • 6,690
  • 20
  • 83
  • 130
0

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
  • 29,278
  • 12
  • 104
  • 130