MySQL Help

[Jeremy]

OK, I will admit I'm not the best with mySQL. Specifically when it comes to changing tables. Here is my question. In updating BBCode Manager, I would like to make it conform more to XenForo and its standards, which involves renaming DB tables from replacementBegin to replacement_begin and such. What kind of a query would accomplish that?

 

[Bob]

just use phpmyadmin. You can change table names with the Operations tab and field names with the structure tab.

 

[Jeremy]

I need a query so I can put it in update code. ;-)

 

[Onimua]

I need a query so I can put it in update code. ;-)

If you do it in phpMyAdmin, does it not spit out the PHP equivalent in the returning message? Use that.

 

[Bob]

I need a query so I can put it in update code. ;-)

here ya go then

http://dev.mysql.com/doc/refman/5.0/en/rename-table.html

12.1.20. RENAME TABLE Syntax
RENAME TABLE tbl_name TO new_tbl_name
[, tbl_name2 TO new_tbl_name2] ...
This statement renames one or more tables.

The rename operation is done atomically, which means that no other session can access any of the tables while the rename is running. For example, if you have an existing table old_table, you can create another table new_table that has the same structure but is empty, and then replace the existing table with the empty one as follows (assuming that backup_table does not already exist):

CREATE TABLE new_table (...);
RENAME TABLE old_table TO backup_table, new_table TO old_table;
If the statement renames more than one table, renaming operations are done from left to right. If you want to swap two table names, you can do so like this (assuming that tmp_table does not already exist):

RENAME TABLE old_table TO tmp_table,
new_table TO old_table,
tmp_table TO new_table;
As long as two databases are on the same file system, you can use RENAME TABLE to move a table from one database to another:

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;
Beginning with MySQL 5.0.2, if there are any triggers associated with a table which is moved to a different database using RENAME TABLE, then the statement fails with the error Trigger in wrong schema.

As of MySQL 5.0.14, RENAME TABLE also works for views, as long as you do not try to rename a view into a different database.

Any privileges granted specifically for the renamed table or view are not migrated to the new name. They must be changed manually.

When you execute RENAME, you cannot have any locked tables or active transactions. You must also have the ALTER and DROP privileges on the original table, and the CREATE and INSERT privileges on the new table.

If MySQL encounters any errors in a multiple-table rename, it does a reverse rename for all renamed tables to return everything to its original state.

You cannot use RENAME to rename a TEMPORARY table. However, you can use ALTER TABLE instead:

mysql> ALTER TABLE orig_name RENAME new_name;

 

[Bob]

If you do it in phpMyAdmin, does it not spit out the PHP equivalent in the returning message? Use that.

It does... one just has to do what I said and they would SEE

RENAME TABLE `xenforo`.`lnblog_entry2` TO `xenforo`.`lnblog_entry` ;

 

[Onimua]

It does... one just has to do what I said and they would SEE

Hey, that looks familiar.

 

[Jeremy]

here ya go then

http://dev.mysql.com/doc/refman/5.0/en/rename-table.html

12.1.20. RENAME TABLE Syntax
RENAME TABLE tbl_name TO new_tbl_name
[, tbl_name2 TO new_tbl_name2] ...
This statement renames one or more tables.

The rename operation is done atomically, which means that no other session can access any of the tables while the rename is running. For example, if you have an existing table old_table, you can create another table new_table that has the same structure but is empty, and then replace the existing table with the empty one as follows (assuming that backup_table does not already exist):

CREATE TABLE new_table (...);
RENAME TABLE old_table TO backup_table, new_table TO old_table;
If the statement renames more than one table, renaming operations are done from left to right. If you want to swap two table names, you can do so like this (assuming that tmp_table does not already exist):

RENAME TABLE old_table TO tmp_table,
new_table TO old_table,
tmp_table TO new_table;
As long as two databases are on the same file system, you can use RENAME TABLE to move a table from one database to another:

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;
Beginning with MySQL 5.0.2, if there are any triggers associated with a table which is moved to a different database using RENAME TABLE, then the statement fails with the error Trigger in wrong schema.

As of MySQL 5.0.14, RENAME TABLE also works for views, as long as you do not try to rename a view into a different database.

Any privileges granted specifically for the renamed table or view are not migrated to the new name. They must be changed manually.

When you execute RENAME, you cannot have any locked tables or active transactions. You must also have the ALTER and DROP privileges on the original table, and the CREATE and INSERT privileges on the new table.

If MySQL encounters any errors in a multiple-table rename, it does a reverse rename for all renamed tables to return everything to its original state.

You cannot use RENAME to rename a TEMPORARY table. However, you can use ALTER TABLE instead:

mysql> ALTER TABLE orig_name RENAME new_name;

I was looking into ALTER TABLE but only knew it could add. Working when tired is bad. haha. And Onimua: I should have thought of that, but I don't use / have phpMyAdmin on my dev install.

 

[ragtek]

If you do it in phpMyAdmin, does it not spit out the PHP equivalent in the returning message? Use that.

exactly
that's how i'm creating my install/upgrade code too^^
(sometimes you have to change the query, because phpmyadmin adds the databasename to the query.

for example: phpmyadmin creates:

CREATE TABLE  `xf`.`ragtek_links` (
`link_id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
`title` VARCHAR( 150 ) NOT NULL ,
`url` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`post_date` INT( 10 ) UNSIGNED NOT NULL ,
`user_id` INT( 10 ) UNSIGNED NOT NULL ,
`node_id` INT( 10 ) UNSIGNED NOT NULL ,
PRIMARY KEY (  `link_id` )
) ENGINE = MYISAM ;

as you see, the database name is included and have to be removed