working on it ...

Filters

Explore Public Snippets

Sort by

Found 5,886 snippets matching: mysql

    public by pan modified Oct 14, 2014  245214  2  5  0

    Replace all occurrences of a string in DB

    Searches and replaces all of occurrences of a string in all tables and all fields of the given Database
    <?php
    /**
    * @kudos http://stackoverflow.com/a/10182498
    */
     
    header("Content-Type: text/plain");
    
    $host = "localhost";
    $username = "root";
    $password = "";
    $database = "mydatabase";
    $string_to_replace  = 'old.example.com';
    $new_string = 'new.example.com';
    
    // Connect to database server
    mysql_connect($host, $username, $password);
    
    // Select database
    mysql_select_db($database);
    
    // List all tables in database
    $sql = "SHOW TABLES FROM ".$database;
    $tables_result = mysql_query($sql);
    
    if (!$tables_result) {
        echo "Database error, could not list tables\nMySQL error: " . mysql_error();
        exit;
    }
    
    echo "In these fields '$string_to_replace' have been replaced with '$new_string'\n\n";
    while ($table = mysql_fetch_row($tables_result)) {
        echo "Table: {$table[0]}\n";
        $fields_result = mysql_query("SHOW COLUMNS FROM ".$table[0]);
        if (!$fields_result) {
            echo 'Could not run query: ' . mysql_error();
            exit;
        }
        if (mysql_num_rows($fields_result) > 0) {
            while ($field = mysql_fetch_assoc($fields_result)) {
                if (stripos($field['Type'], "VARCHAR") !== false || stripos($field['Type'], "TEXT") !== false) {
                    echo "  ".$field['Field']."\n";
                    $sql = "UPDATE ".$table[0]." SET ".$field['Field']." = replace(".$field['Field'].", '$string_to_replace', '$new_string')";
                    mysql_query($sql);
                }
            }
            echo "\n";
        }
    }
    
    mysql_free_result($tables_result);

    public by JaredNeil modified Jan 23, 2015  142339  0  4  0

    MySQL Query

    MySQL Query: gistfile1.sql
    SELECT *
    FROM 
    (
    	SELECT id, sort_column, numbers_column
    	FROM table_1, table_2
    	WHERE table_1.this_column = table_2.that_column
    	ORDER BY sort_column
    ) as results
    UNION ALL
    SELECT *
    FROM
    (
    	SELECT "Total" as id, "" as sort_column, SUM(numbers_column) as numbers_column
    	FROM table_1, table_2
    	WHERE table_1.this_column = table_2.that_column
    ) as total;
    
    

    public by boloka modified Feb 22, 2015  136421  0  5  0

    INSERT Mysql

    only one row
    INSERT INTO table_name (column1,column2,column3,...)
    VALUES (value1,value2,value3,...);

    public by cghersi modified Sep 26, 2013  11229  221  6  1

    List of Variables to be set in MySQL for performance tuning

    This is the most important variables to be set in MySQL for performance tuning
    Set global thread_cache_size = 4;
    Set global query_cache_size = 1024*1024*1024;
    Set global query_cache_limit=768*1024;
    Set global query_cache_min_res_unit = 2048;
    Set long_query_time = 5;
    Set global wait_timeout = 7800;
    Set global key_buffer_size = 512*1024*1024;
    Set global table_open_cache = 800;
    SET GLOBAL low_priority_updates=1;
    SET GLOBAL concurrent_insert=ALWAYS;
    SET GLOBAL tmp_table_size = 67108864;
    SET GLOBAL max_heap_table_size = 67108864;
    SET GLOBAL table_definition_cache = 1024;

    public by sebastian.piskorski modified Jan 17, 2017  3362  3  5  2

    [MySQL] Calculate Database size

    [MySQL] Calculate Database size using simple single query.
    SELECT table_schema "Data Base Name",
        sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
        sum( data_free )/ 1024 / 1024 "Free Space in MB"
    FROM information_schema.TABLES
    GROUP BY table_schema ;

    public by pan modified Sep 25, 2014  2302  1  6  0

    Instantiate Date from a mysql datetime string

    Adds the ability to Date to instatiate from a mysql datetime string. String should be in format "Y-M-D H:M:S" or "Y-M-D".
    /**
         * Instantiate Date from a mysql datetime string
         * @param string mysql_string eg "Y-M-D H:M:S" or "Y-M-D"
         * @returns {Date|null}
         * @kudos http://stackoverflow.com/a/5058318
         */
        Date.createFromMysql = function(mysql_string)
        {
            if(typeof mysql_string === 'string')
            {
                var t = mysql_string.split(/[- :]/);
    
                //when t[3], t[4] and t[5] are missing they defaults to zero
                return new Date(t[0], t[1] - 1, t[2], t[3] || 0, t[4] || 0, t[5] || 0);
            }
    
            return null;
        }

    public by cghersi modified Jul 30, 2012  8493  30  7  0

    MySQL SELECT statement from blob to string

    SELECT statement to translate a blob field into a readable string
    CREATE TABLE tbl (myBlob longblob);
    
    INSERT INTO tbl VALUES (0x1234);
    
    SELECT 	HEX(myBlob) AS PrintableBlob
    FROM	tbl;

    public by cghersi modified Jul 30, 2012  6439  12  7  0

    MySQL SELECT Statement From time in milliseconds to date

    The clause in SELECT statement to be used to convert a dateetime stores as milliseconds from Epoch into a Date field
    CREATE TABLE tbl (myTime bigint(20));
    
    INSERT INTO tbl VALUES (1343400240000);
    
    SELECT 	FROM_UNIXTIME(myTime /1000) AS MyDate
    FROM	tbl;

    public by cghersi modified Jan 4, 2013  2621  1  7  0

    Call Stored Procedure with OUT or INOUT parameters in MySQL

    This is how to call a stored procedure "MySP" with a parameter defined as INOUT directyl from SQL environment
    DELIMITER $$
    CREATE PROCEDURE MySP(
    	IN _Ip VARCHAR(20),
    	INOUT _Id INT
    )
    BEGIN
    	SELECT  Id INTO _Id 
    	FROM    `MyTable`
    	WHERE   Ip = _Ip ;
    END $$
    DELIMITER ;
    
    SET @a = 1;
    CALL MySP('192.168.2.3', @a);

    public by cghersi modified Jun 25, 2013  2104  2  6  1

    Communication with UTF-8 for MySQL use in Java

    This is the way to use UTF-8 for the communiocation of a Java application towards a MySQL database
    Class.forName("com.mysql.jdbc.Driver").newInstance();//call jdbc driver
    
    String url = "jdbc:mysql://localhost/unicode?useUnicode=true&characterEncoding=UTF-8";//getting url
                
    conn = DriverManager.getConnection(url,"root","");//create connection
    
    • Public Snippets
    • Channels Snippets