Is there a naming convention for MySQL?

Question

I would say that first and foremost: be consistent.

I reckon you are almost there with the conventions that you have outlined in your question. A couple of comments though:

Points 1 and 2 are good I reckon.

Point 3 - sadly this is not always possible. Think about how you would cope with a single table foo_bar that has columns foo_id and another_foo_id both of which reference the foo table foo_id column. You might want to consider how to deal with this. This is a bit of a corner case though!

Point 4 - Similar to Point 3. You may want to introduce a number at the end of the foreign key name to cater for having more than one referencing column.

Point 5 - I would avoid this. It provides you with little and will become a headache when you want to add or remove columns from a table at a later date.

Some other points are:

Index Naming Conventions

You may wish to introduce a naming convention for indexes - this will be a great help for any database metadata work that you might want to carry out. For example you might just want to call an index foo_bar_idx1 or foo_idx1 - totally up to you but worth considering.

Singular vs Plural Column Names

It might be a good idea to address the thorny issue of plural vs single in your column names as well as your table name(s). This subject often causes big debates in the DB community. I would stick with singular forms for both table names and columns. There. I've said it.

The main thing here is of course consistency!

Consistency is the key to any naming standard. As long as it's logical and consistent, you're 99% there.

The standard itself is very much personal preference - so if you like your standard, then run with it.

To answer your question outright - no, MySQL doesn't have a preferred naming convention/standard, so rolling your own is fine (and yours seems logical).

MySQL has a short description of their more or less strict rules:

https://dev.mysql.com/doc/internals/en/coding-style.html

Most common codingstyle for MySQL by Simon Holywell:

http://www.sqlstyle.guide/

See also this question: Are there any published coding style guidelines for SQL?

Thankfully, PHP developers aren't "Camel case bigots" like some development communities I know.

Your conventions sound fine.

Just so long as they're a) simple, and b) consistent - I don't see any problems :)

PS: Personally, I think 5) is overkill...

Simple Answer: NO

Well, at least a naming convention as such encouraged by Oracle or community, no, however, basically you have to be aware of following the rules and limits for identifiers, such as indicated in MySQL documentation: https://dev.mysql.com/doc/refman/8.0/en/identifiers.html

About the naming convention you follow, I think it is ok, just the number 5 is a little bit unnecesary, I think most visual tools for managing databases offer a option for sorting column names (I use DBeaver, and it have it), so if the purpouse is having a nice visual presentation of your table you can use this option I mention.

By personal experience, I would recommed this:

• Use lower case. This almost ensures interoperability when you migrate your databases from one server to another. Sometimes the lower_case_table_names is not correctly configured and your server start throwing errors just by simply unrecognizing your camelCase or PascalCase standard (case sensitivity problem).
• Short names. Simple and clear. The most easy and fast is identify your table or columns, the better. Trust me, when you make a lot of different queries in a short amount of time is better having all simple to write (and read).
• Avoid prefixes. Unless you are using the same database for tables of different applications, don't use prefixes. This only add more verbosity to your queries. There are situations when this could be useful, for example, when you want to indentify primary keys and foreign keys, that usually table names are used as prefix for id columns.
• Use underscores for separating words. If you still want to use more than one word for naming a table, column, etc., so use underscores for separating_the_words, this helps for legibility (your eyes and your stressed brain are going to thank you).
• Be consistent. Once you have your own standard, follow it. Don´t be the person that create the rules and is the first who breaking them, that is shameful.

And what about the "Plural vs Singular" naming? Well, this is most a situation of personal preferences. In my case I try to use plural names for tables because I think a table as a collection of elements or a package containig elements, so a plural name make sense for me; and singular names for columns because I see columns as attributes that describe singularly to those table elements.