MySQL How To Duplicate A Table – Fast Tips
Recently I had to deal with some limitations of my database provider that doesn’t support table renaming. So I had to duplicate a table manually.
The database of my SaaS platform is hosted on Planetscale. The company provides a MySQL-compatible serverless database. Thanks to its serverless nature you can get the power of horizontal sharding, non-blocking schema changes, and many more powerful database features without the pain of implementing them. And a great developer experience.
From another perspective you have to deal with some constraint regarding schema changes. These limitations are needed to guarantee consistency in a sharded environment.
They made a lot of progress since I became a customer (almost two years ago), like the support for Foreign Key constraint: https://planetscale.com/docs/concepts/foreign-key-constraints
Rename a table
Inspector is a Laravel application. Using the Laravel migrations I could use the rename function to simply change the name of a table:
Schema::rename('from', 'to');
Planetscale doesn't support table renaming natively. So I had to find a workaround to accomplish the task.
To be honest renaming a table is a quite rare operation. For me it was because of an overlap of names between "Projects" and "Applications" entities. I had to rename Projects -> Applications.
How to duplicate a table in MySQL
There are two ways to duplicate a table in MySQL.
Duplicate the table structure
You can duplicate only the table structure (columns, keys, indexes, etc) without data, using CREATE TABLE … LIKE:
CREATE TABLE applications LIKE projects;
The result is the creation of the applications table with the exact same structure of the original projects table, but WITH NO data.
To import data too you can run a second statement as INSERT INTO … SELECT:
INSERT INTO applications SELECT * FROM projects;
Take care running this statement on big tables because it can take a lot of time and server resources.
Duplicate only column definition
The second option is to duplicate only column definitions and import data in one statement using CREATE TABLE … AS SELECT:
CREATE TABLE applications AS SELECT * FROM projects;
The new applications table inherits only the basic column definitions from the projects table. It does not duplicate Foreign Key constraints, indexes, and auto_increment definitions.
This option could be usefult when you have the names of indexes and keys related to the table name. Changing the name of the table you have to replace the names of the constraints. You’d better not import them at all and do it all over again.
Resources
Database is always an hot topic for developers at any stage. You can find other technical resources on the blog. Here are the most popular articles on the topic:
- Resolved - Integrity constraint violation
- Save 1 million queries with Laravel eager loading
- How to scale a SQL database
- Resolved – MySQL lock wait timeout exceeded using Laravel queues and jobs
New To Inspector? Monitor your application for free
Inspector is a Code Execution Monitoring tool specifically designed for software developers. You don't need to install anything at the server level, just install the composer package and you are ready to go.
Unlike other complex, all-in-one platforms, Inspector is super easy, and PHP friendly. You can try our Laravel or Symfony package.
If you are looking for effective automation, deep insights, and the ability to forward alerts and notifications into your messaging environment try Inspector for free. Register your account.
Or learn more on the website: https://inspector.dev
The above is the detailed content of MySQL How To Duplicate A Table – Fast Tips. For more information, please follow other related articles on the PHP Chinese website!

Hot AI Tools

Undress AI Tool
Undress images for free

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Common problems and solutions for PHP variable scope include: 1. The global variable cannot be accessed within the function, and it needs to be passed in using the global keyword or parameter; 2. The static variable is declared with static, and it is only initialized once and the value is maintained between multiple calls; 3. Hyperglobal variables such as $_GET and $_POST can be used directly in any scope, but you need to pay attention to safe filtering; 4. Anonymous functions need to introduce parent scope variables through the use keyword, and when modifying external variables, you need to pass a reference. Mastering these rules can help avoid errors and improve code stability.

There are three common methods for PHP comment code: 1. Use // or # to block one line of code, and it is recommended to use //; 2. Use /.../ to wrap code blocks with multiple lines, which cannot be nested but can be crossed; 3. Combination skills comments such as using /if(){}/ to control logic blocks, or to improve efficiency with editor shortcut keys, you should pay attention to closing symbols and avoid nesting when using them.

The key to writing PHP comments is to clarify the purpose and specifications. Comments should explain "why" rather than "what was done", avoiding redundancy or too simplicity. 1. Use a unified format, such as docblock (/*/) for class and method descriptions to improve readability and tool compatibility; 2. Emphasize the reasons behind the logic, such as why JS jumps need to be output manually; 3. Add an overview description before complex code, describe the process in steps, and help understand the overall idea; 4. Use TODO and FIXME rationally to mark to-do items and problems to facilitate subsequent tracking and collaboration. Good annotations can reduce communication costs and improve code maintenance efficiency.

AgeneratorinPHPisamemory-efficientwaytoiterateoverlargedatasetsbyyieldingvaluesoneatatimeinsteadofreturningthemallatonce.1.Generatorsusetheyieldkeywordtoproducevaluesondemand,reducingmemoryusage.2.Theyareusefulforhandlingbigloops,readinglargefiles,or

TolearnPHPeffectively,startbysettingupalocalserverenvironmentusingtoolslikeXAMPPandacodeeditorlikeVSCode.1)InstallXAMPPforApache,MySQL,andPHP.2)Useacodeeditorforsyntaxsupport.3)TestyoursetupwithasimplePHPfile.Next,learnPHPbasicsincludingvariables,ech

ToinstallPHPquickly,useXAMPPonWindowsorHomebrewonmacOS.1.OnWindows,downloadandinstallXAMPP,selectcomponents,startApache,andplacefilesinhtdocs.2.Alternatively,manuallyinstallPHPfromphp.netandsetupaserverlikeApache.3.OnmacOS,installHomebrew,thenrun'bre

In PHP, you can use square brackets or curly braces to obtain string specific index characters, but square brackets are recommended; the index starts from 0, and the access outside the range returns a null value and cannot be assigned a value; mb_substr is required to handle multi-byte characters. For example: $str="hello";echo$str[0]; output h; and Chinese characters such as mb_substr($str,1,1) need to obtain the correct result; in actual applications, the length of the string should be checked before looping, dynamic strings need to be verified for validity, and multilingual projects recommend using multi-byte security functions uniformly.

You can use substr() or mb_substr() to get the first N characters in PHP. The specific steps are as follows: 1. Use substr($string,0,N) to intercept the first N characters, which is suitable for ASCII characters and is simple and efficient; 2. When processing multi-byte characters (such as Chinese), mb_substr($string,0,N,'UTF-8'), and ensure that mbstring extension is enabled; 3. If the string contains HTML or whitespace characters, you should first use strip_tags() to remove the tags and trim() to clean the spaces, and then intercept them to ensure the results are clean.
