Google+

MySQL ( MySQL Cheat Sheet) #

MySQL  ( MySQL Cheat Sheet)

MySQL, , , .
PNG

/ : mysql / 25.02.14 / [0]

MySQL (bigdata) #

MySQL. mysql . , . .

:
HOW VARIABLES LIKE '%partition%';
have_partitioning | YES


mySQL?
1. RANGE
PARTITION BY RANGE (store_id) (
 PARTITION p0 VALUES LESS THAN (10),
 PARTITION p1 VALUES LESS THAN (20),
 PARTITION p3 VALUES LESS THAN (30)
 );

2. LIST
 PARTITION BY LIST(store_id) (
 PARTITION pNorth VALUES IN (3,5,6,9,17),
 PARTITION pEast VALUES IN (1,2,10,11,19,20)
 )

3. HASH
, , .
 PARTITION BY HASH(store_id)
 PARTITIONS 4;

4. KEY
HASH, .
 PARTITION BY KEY(s1)
 PARTITIONS 10;

RANGE DATETIME.
ALTER TABLE flows
PARTITION BY RANGE( TO_DAYS(date) ) (
 PARTITION y2013m10 VALUES LESS THAN( TO_DAYS('2013-10-01') ),
 PARTITION y2013m11 VALUES LESS THAN( TO_DAYS('2013-11-01') ),
 PARTITION y2013m12 VALUES LESS THAN( TO_DAYS('2013-12-01') )
);


:
[Error] 1503 - A PRIMARY KEY must include all columns in the table partitioning function


, PRIMARY KEY, .
1 . =(

id , - . 2013 . .
ALTER TABLE flows
PARTITION BY RANGE (id) (
        PARTITION y2013 VALUES LESS THAN( 231607217 ),
        PARTITION y2014 VALUES LESS THAN(MAXVALUE)
);

? , 2013 , .

, . , .

: http://www.rldp.ru/mysql/mysqlpro/parts.htm

/ : mysql / 19.02.14 / [0]

mysql (duplicate entry) #

CREATE TABLE clean LIKE options

ALTER IGNORE TABLE clean ADD UNIQUE INDEX (add1, add2, add3)

IGNORE
INSERT IGNORE INTO clean SELECT * FROM options

DROP TABLE options

RENAME TABLE clean TO options


/ : mysql / 14.01.14 / [0]

MySQL #

ON DELETE CASCADE
CREATE TABLE test1 ( 
   id INTEGER NOT NULL, 
   parent INTEGER, 
   PRIMARY KEY (id), 
   FOREIGN KEY (parent) REFERENCES test1 (id) 
     ON UPDATE CASCADE 
     ON DELETE CASCADE 
 ); 
ON DELETE CASCADE, PARENT. , , .

ON DUPLICATE KEY
//  
 $row = query('SELECT * FROM table WHERE id=1');
 //     
 if ($row) {
 //  
 query('UPDATE table SET column=column+1 WHERE id=1');
 }
 else {
 //  
 query('INSERT INTO table SET column=1, id=1');
 }
php
INSERT INTO table SET column = 1, id=1 ON DUPLICATE KEY UPDATE column = column + 1


INSERT IGNORE
//  
 $row = query('SELECT * FROM table WHERE id=1');
 //    ,    
 if (!$row) {
 query('INSERT INTO table ');
 }
php
INSERT IGNORE INTO table 

/ : mysql / 25.07.13 / [0]

MySQL Event Scheduler #

MySQL Event Scheduler

(!) , () , :
- 1-
- 1-
-
-
, , 4 , , .

2 ( ). , - , - .
MySQL Event Scheduler DAEMON!

1) MySQL Event Scheduler
SET GLOBAL event_scheduler = 1;

MySQL Event Scheduler


2) Event-
CREATE EVENT antibrut
ON SCHEDULE EVERY 1 HOUR
DO
DELETE FROM _antibrut WHERE logintime < DATE_SUB(NOW(),INTERVAL 1 HOUR)

CREATE EVENT antispam
ON SCHEDULE EVERY 1 HOUR
DO
DELETE FROM _antispam WHERE time < DATE_SUB(NOW(),INTERVAL 1 HOUR)



Event-:
SHOW EVENTS;


Event, ALTER, :
ALTER antibrut 
ON SCHEDULE EVERY 5 HOUR 
STARTS TIMESTAMP + 3 HOUR 


Event:
DROP EVENT antispam;

/ : MySQL, Event, Scheduler / 05.01.13 / [2]

ShopOS #

    ShopOS

:
http://www.getinfo.ru/article610.html - !
http://phpclub.ru/detail/article/db_tree

, ShopOS. , ( ), , . .

:
    ShopOS

. . , . , .

( index.html ):

    {php}
    $sql = "SELECT cat.categories_id, cd.categories_name FROM vip_categories cat, vip_categories_description cd WHERE cat.categories_status = '1' AND cd.categories_id = cat.categories_id";
    $sql = osDBquery($sql);
    while ($row = os_db_fetch_array($sql)) {
    $menu[$row['categories_id']] = $row['categories_name'];
    }
    foreach ($menu as $CatId => $CatName )
    {
    echo "
  • ".$CatName."";
    echo "
      ";
      $query = "SELECT * FROM
      vip_products p,
      vip_products_description pd,
      vip_products_to_categories p2c,
      vip_categories c
      WHERE
      c.categories_status='1'
      and p.products_status = '1'
      and p.products_id = p2c.products_id
      and p.products_id=pd.products_id
      and p2c.categories_id = '".$CatId."'
      and c.categories_id = '".$CatId."'
      ORDER by p.products_date_added";
      $query = osDBquery($query);
      while ($row1 = os_db_fetch_array($query)) {
      echo "
    • ".$row1['products_name']."
    • ";
      }
      echo "
  • ";
    }
    {/php}


:


, !
    ShopOS

! .

( index.html ):

    {php}
    $sql = "SELECT cat.categories_id, cat.parent_id, cat.categories_url, cd.categories_name FROM vip_categories cat, vip_categories_description cd WHERE cat.categories_status = '1' AND cd.categories_id = cat.categories_id ORDER BY cat.parent_id, cat.categories_id";
    $sql = osDBquery($sql);
    while ($row = os_db_fetch_array($sql)) {
    $menu[$row['parent_id']][$row['categories_id']] = $row['categories_name'];
    }
    foreach ($menu[0] as $CatId => $CatName )
    {
    echo "
  • ".$CatName."";
    if (isset($menu[$CatId])) {
    echo "
      ";
      foreach ($menu[$CatId] as $ItemId => $ItemName)
      {
      echo "
    • ".$ItemName."";
      echo "
        ";
        $query = "SELECT * FROM
        vip_products p,
        vip_products_description pd,
        vip_products_to_categories p2c,
        vip_categories c
        WHERE
        c.categories_status='1'
        and p.products_status = '1'
        and p.products_id = p2c.products_id
        and p.products_id=pd.products_id
        and p2c.categories_id = '".$ItemId."'
        and c.parent_id = '".$CatId."'
        ORDER by p.products_date_added";
        $query = osDBquery($query);
        while ($row1 = os_db_fetch_array($query)) {
        echo "
      • ".$row1['products_name']."
      • ";
        }
        echo "
      ";
      echo "
    • ";
      }
      echo "
  • ";
    } else { echo "";}
    }
    {/php}


:



- ( index.html ):


    {php}
    $sql = "SELECT cat.categories_id, cat.parent_id, cat.categories_url, cd.categories_name FROM vip_categories cat, vip_categories_description cd WHERE cat.categories_status = '1' AND cd.categories_id = cat.categories_id ORDER BY cat.parent_id, cat.categories_id";
    $sql = osDBquery($sql);
    while ($row = os_db_fetch_array($sql)) {
    $menu[$row['parent_id']][$row['categories_id']] = $row['categories_name'];
    }
    foreach ($menu[0] as $CatId => $CatName )
    {
    echo "
  • ".$CatName."";
    if (isset($menu[$CatId])) {
    echo "
      ";
      foreach ($menu[$CatId] as $ItemId => $ItemName)
      {
      echo "
    • ".$ItemName."";
      echo "
        ";
        $query = "SELECT * FROM
        vip_products p,
        vip_products_description pd,
        vip_products_to_categories p2c,
        vip_categories c
        WHERE
        c.categories_status='1'
        and p.products_status = '1'
        and p.products_id = p2c.products_id
        and p.products_id=pd.products_id
        and p2c.categories_id = '".$ItemId."'
        and c.parent_id = '".$CatId."'
        ORDER by p.products_date_added";
        $query = osDBquery($query);
        while ($row1 = os_db_fetch_array($query)) {
        //echo "
        ";
        //print_r($row1);
        //echo "
        ";
        echo "
      • ".$row1['products_name']."
      • ";
        }
        echo "
      ";
      echo "
    • ";
      }
      echo "
  • ";
    } else { echo "";}
    }
    {/php}


:

, . , ( UL), .

, . {$box_CATEGORIES}.

_\themes\_\source\inc\show_category.inc.php. $MaxLevel 1, , 1 .
:
if ($old_level > $level) {

:
if ( isset($GLOBALS['cPath']) ) {
if ( !strpos($GLOBALS['cPath'], "_")===false ) {
$path = explode('_',$GLOBALS['cPath']);
$query = "SELECT * FROM
vip_products p,
vip_products_description pd,
vip_products_to_categories p2c,
vip_categories c
WHERE
c.categories_status='1'
and p.products_status = '1'
and p.products_id = p2c.products_id
and p.products_id=pd.products_id
and p2c.categories_id = '".$path[1]."'
and c.parent_id = '".$path[0]."'
ORDER by p.products_date_added";
$query = osDBquery($query);
$categories_string .= "";
}
}

: cPath ( ), , , . - .
    ShopOS

, . -) .

UPD 02.09.11. + .
show_category.inc.php ( $foo[$cid]['name'].$ProductsCount. ''; - 114 )
if ($level == 3) {
if ( !empty($Aktiv)) {
//$categories_string .= $cid;
$query = "SELECT * FROM
vip_products_to_categories p2c,
vip_products p,
vip_products_description pd
WHERE
p2c.categories_id = '".$cid."'
and p.products_status = '1'
and p.products_id = p2c.products_id
and p.products_id=pd.products_id
ORDER by p.products_date_added";
$query = osDBquery($query);
$categories_string .= "
    ";
    while ($row1 = os_db_fetch_array($query)) {
    $categories_string .= "
  • [url=/product_info.php?products_id={$row1[]".$row1['products_name']."[/url]";
    }
    $categories_string .= "
";
}
}

PHP, / : mysql, smarty, db_tree, shopos / 07.06.11 / [0]