1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL Help

Discussion in 'XenForo Development Discussions' started by Jeremy, Mar 9, 2011.

  1. Jeremy

    Jeremy XenForo Moderator Staff Member

    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?
  2. Bob

    Bob Well-Known Member

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

    Jeremy XenForo Moderator Staff Member

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

    Onimua Well-Known Member

    If you do it in phpMyAdmin, does it not spit out the PHP equivalent in the returning message? Use that. :p
    Bob likes this.
  5. Bob

    Bob Well-Known Member

    here ya go then :p


    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;
  6. Bob

    Bob Well-Known Member

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

  7. Onimua

    Onimua Well-Known Member

    Hey, that looks familiar. :p
    Bob likes this.
  8. Jeremy

    Jeremy XenForo Moderator Staff Member

    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.
    Bob likes this.
  9. ragtek

    ragtek Guest

    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` (
    `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` )
    as you see, the database name is included and have to be removed

Share This Page