You Are Here Home > MySQL

MySQL

Rearrange/move MySQL Table Columns

To *move* a column to a particular position:

ALTER TABLE `user`
MODIFY COLUMN `permission` int(10)
AFTER `password`

To *move* a column to the beginning:

ALTER TABLE `user`
MODIFY COLUMN `permission` int(10)
FIRST
Rearrange/move MySQL Table Columns
Comments (0)   Filed under: MySQL   Posted by: Hamid on October 19, 2009

Add a Column To The Beginning Of a Table

Assume that we want to add a column ‘id’ in the beginning of the table ‘test’:

ALTER TABLE test ADD id INT NOT NULL FIRST;
Add a Column To The Beginning Of a Table
Comments (0)   Filed under: MySQL   Posted by: Codehead on September 18, 2009

Change or Reset The Auto Increment Value

Here is how to change the auto increment value to say 10 on the table ‘test’:

ALTER TABLE test AUTO_INCREMENT 1;
Change or Reset The Auto Increment Value
Comments (0)   Filed under: MySQL   Posted by: Codehead on

Add a Column After a Specific Column On a Table

Assume that we want to add a column ‘number’ after ‘pass’ column on table ‘test’:

ALTER TABLE test ADD number INT(12) NOT NULL AFTER pass;
Add a Column After a Specific Column On a Table
Comments (0)   Filed under: MySQL   Posted by: Codehead on

Copy An Entire Table – Data & Structure

Assume that we want to copy our ‘user’ table to a table called ‘backup’:

CREATE TABLE backup LIKE user;
INSERT INTO backup SELECT * FROM user;
Copy An Entire Table – Data & Structure
Comments (0)   Filed under: MySQL   Posted by: Codehead on

Copy Table Data ONLY

Assume that we want to copy all the data from the table ‘user’ into ‘backup’ table:

INSERT INTO backup SELECT * FROM user;
Copy Table Data ONLY
Comments (0)   Filed under: MySQL   Posted by: Codehead on

Copy a Table Structure ONLY

Assume that we want to copy the table ‘user’’s structure into a new table called ‘backup’:

CREATE TABLE backup LIKE user;
Copy a Table Structure ONLY
Comments (0)   Filed under: MySQL   Posted by: Codehead on

Create An Index

Assuming you want to create the index ‘name’ on column ‘name’ on table ‘test’:

ALTER TABLE test ADD INDEX name (name) ;

Where the first ‘name’ is the name of the index and the ‘name’ in parenthesis is the column name.

If you want to index 2 columns:

ALTER TABLE test ADD INDEX name (name, pass) ;
Create An Index
Comments (0)   Filed under: MySQL   Posted by: Codehead on

Drop An Index

Assuming you are deleting the ‘name’ index on ‘test’ table:

ALTER TABLE `test` DROP INDEX `name`;
Drop An Index
Comments (0)   Filed under: MySQL   Posted by: Codehead on

A Tiny MySQL++ Tutorial; C++ and MySQL; MySQL++ Example

I wrote a post about how to install MySQL++ and I thought I will write a quick tutorial on how to use it too.

This is a very basic MySQL++ program and it’s very self explanatory:

#include <mysql++.h>
#include <stdlib.h>
 
using namespace std;
using namespace mysqlpp;
 
int main() {
    try {
        Connection conn(false);
        conn.connect("DB NAME", "DB HOST probably localhost", "DB USER", "DB PASS");
        Query query = conn.query();
    } catch (BadQuery er) { // handle any connection or
        // query errors that may come up
        cerr << "Error: " << er.what() << endl;
        return -1;
    } catch (const BadConversion& er) {
        // Handle bad conversions
        cerr << "Conversion error: " << er.what() << endl <<
                "\tretrieved data size: " << er.retrieved <<
                ", actual size: " << er.actual_size << endl;
        return -1;
    } catch (const Exception& er) {
        // Catch-all for any other MySQL++ exceptions
        cerr << "Error: " << er.what() << endl;
        return -1;
    }
 
    return (EXIT_SUCCESS);
}

This will connect to your database and creates a query object ready to go. Save this as test.cpp

To compile this, you will have to create a Makefile in the same folder, so create a file and name it “Makefile” (no quotes) and add these to it:

CXX := g++
CXXFLAGS := -I/usr/include/mysql -I/usr/local/include/mysql++
LDFLAGS := -L/usr/local/lib -lmysqlpp -lmysqlclient -lnsl -lz -lm
EXECUTABLE := main
 
all: test
 
clean:
	rm -f $(EXECUTABLE) *.o

If you get funny errors, make sure there are no extra spaces in this file, and there is only one “tab” behind the line: rm -f $(EXECUTABLE) *.o

Now, you can make and run this by doing:
make
./test

Now, I’m going to show you how to execute some queries and you can go ahead and experiment on your own tables.

Here is a INSERT query followed by a SELECT; this will hopefully cover a lot, because INSERT is a type of query that doesn’t return anything and you need to escape values in order to INSERT.
SELECT on the other hand returns rows, although there are 3 ways that they can be done but here is a simple way that works for me.

#include <mysql++.h>
#include <stdlib.h>
 
using namespace std;
using namespace mysqlpp;
 
int main() {
    try {
        Connection conn(false);
        conn.connect("DB NAME", "DB HOST probably localhost", "DB USER", "DB PASS");
        Query query = conn.query();
 
        /* To insert stuff with escaping */
        query << "INSERT INTO some_table " <<
                     "VALUES (" <<
                     "'', " << /* This is left empty because the column is AUTO_INCREMENT */
                     "\"" << escape << some_var_that_contains_some_value << "\"" <<
                     ");";
        query.execute();
        /* That's it for INSERT */
 
        /* Now SELECT */
        query << "SELECT * FROM biz LIMIT 10";
        StoreQueryResult ares = query.store();
        for (size_t i = 0; i < ares.num_rows(); i++)
           cout << "Name: " << ares[i]["name"] << " - Address: " << ares[i]["address"] << endl;
 
        /* Let's get a count of something */
        query << "SELECT COUNT(*) AS row_count FROM biz";
        StoreQueryResult bres = query.store();
        cout << "Total rows: " << bres[0]["row_count"];
 
    } catch (BadQuery er) { // handle any connection or
        // query errors that may come up
        cerr << "Error: " << er.what() << endl;
        return -1;
    } catch (const BadConversion& er) {
        // Handle bad conversions
        cerr << "Conversion error: " << er.what() << endl <<
                "\tretrieved data size: " << er.retrieved <<
                ", actual size: " << er.actual_size << endl;
        return -1;
    } catch (const Exception& er) {
        // Catch-all for any other MySQL++ exceptions
        cerr << "Error: " << er.what() << endl;
        return -1;
    }
 
    return (EXIT_SUCCESS);
}

Again, whenever you want to run your code do:
make
./test

I wrote this because something like this would help myself a lot.

Good Luck :)

Update

query.reset();

Will reset the query object; this is useful when you are (in your program) generating a MySQL query but must discard it and generate another query (based on some condition).

A Tiny MySQL++ Tutorial; C++ and MySQL; MySQL++ Example
Comments (13)   Filed under: C/C++, MySQL, Programming   Posted by: Codehead on February 25, 2009
Older Posts »