CODEWITHSUNDEEP
mysqlmariadblimits
ebyrob
ebyrob

Reputation: 674

How to increase maximum identifier name length?

Table names, Column names, Index names, etc... In MySQL (and MariaDB) these have a maximum length of 64 characters. How can I increase that?

Duplicate here: Maximum length of a Column name in MySQL

MySQL docs here: https://dev.mysql.com/doc/refman/5.7/en/identifiers.html

MariaDB docs here: https://mariadb.com/kb/en/mariadb/identifier-names/

Troublesome answer: Switch to PostgreSQL and recompile.

Background: Column names prefixed with table names combined with table names prefixed with sub-project names. Usually project names are short, but two just collided and at least one of them will get a bit longer.

Example: 

/* One MySQL Instance for in-house applications called "MySQL" on port 3306.  
 * One MySQL schema (database / catalog) per application "intranet_website".
 * Several sub-project prefixes per application, example: "finance_"
 * Individual table-name: "invoice"
 * Specific column-name: "TotalAmount"  ****/
CREATE TABLE intranet_website.finance_invoice_tbl (
    -- ...
    finance_invoice_TotalAmount DECIMAL(20,2),  -- 27 chars
    -- ...
)

This may seem excessive, but consider Java naming conventions, or even just .Net. 

com.companyname.intranetwebsite.finance.invoice.getTotalAmount() // 63 chars
IntranetWebsite.Finance.Invoice.GetTotalAmount() // 47 chars

Note: It isn't my choice to prefix columns with table names. However, there are a few cases where this becomes marginally useful for joins without re-joins of the same table. I know of at least one concrete instance where code writing SQL-code was much easier to implement due to these prefixes.

Upvotes: 3

Views: 8499

Answers (1)

Mike Nakis
Mike Nakis

Reputation: 61993

It probably cannot be done, as others have already suggested in comments.

However, you should not need to do this.

Column names prefixed with table names are completely unnecessary, since SQL offers the tablename.columname syntax that you can use to differentiate between columns. Actually, most programmers I know would wholeheartedly agree that prefixing each column name with the name of the table to which it belongs would be very annoying.

table names prefixed with project names are also unnecessary, because that's what catalogs / schemas are for. Also, most RDBMSes support the catalogname.tablename syntax. If you have multiple projects that share tables, and your RDBMS does not support cross-catalog relations, then I would suggest that you have a project refactoring issue, or a choice-of-RDBMS issue, not a table naming issue.

Also, if you think 64 character identifiers are short, you should try Oracle. Oh, the fun you will have!

Upvotes: 6

Related Questions