Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
MySQLi Articles
Page 335 of 341
How to handle fragmentation of auto increment ID column in MySQL?
Whenever we renumber, there might be a problem. There is a need to declare a unique ID for a column. In MySQL version 5.6 InnoDB, we can reuse the auto_increment ID by including the ID column in an INSERT statement and we can give any specific value that we want. The situations are as follows − Whenever we delete the ID with the highest number Whenever we start and stop MySQL server Whenever we insert a new record Example of ID auto increment using auto_increment variable. mysql> create table UniqueAutoId -> ( ...
Read MoreMethods for tracking database schema changes in MySQL?
Whenever a table is present in a project with a single database, we can do database schema changes using schema version or migration. It aims to keep track of database schema changes or structural changes. The table creation to keep track of schema changes. mysql> create table SchemaDatabaseMethodDemo -> ( -> `WhenTime` timestamp not null default CURRENT_TIMESTAMP, -> `TheKey` varchar(200) not null, -> `Version` varchar(200), -> primary key(`TheKey`) -> )ENGINE=InnoDB; Query OK, 0 rows affected (0.45 sec) Inserting records into ...
Read MoreHow do I get the current time zone of MySQL?
The following is the syntax to get the current time zone of MySQL. mysql> SELECT @@global.time_zone, @@session.time_zone; The following is the output. +--------------------+---------------------+ | @@global.time_zone | @@session.time_zone | +--------------------+---------------------+ | SYSTEM | SYSTEM | +--------------------+---------------------+ 1 row in set (0.00 sec) The above just returns “SYSTEM” because MySQL is set for system time zones. Alternately, we can get the current time zone with the help of now() function. Let us first ...
Read Moremysql_fetch_array vs mysql_fetch_assoc vs mysql_fetch_object?
These mysql_* functions are deprecated and other functions are available that gives better security and functionality. Note: As an alternative, use either _assoc or _row though. mysql_fetch_assoc The function returns an associative array of strings that corresponds to the fetched row, or FALSE if there are no more rows. The associativity array tells us about the key value pair, whereas the key tells about any column name and the value tells about the row value. Here we can map the column name as key and value as row. For example. Key is ID and value is corresponding name. ...
Read MoreGet table names using SELECT statement in MySQL?
To get table names using SELECT statement, use “information_schema.tables”. Let us see an example, wherein we have a database that contains 3 tables. The syntax to get all table names with the help of SELECT statement. SELECT Table_name as TablesName from information_schema.tables where table_schema = 'yourDatabaseName'; Using database “test”, and applying the above syntax to get the table names using SELECT mysql> use test; Database changed mysql> SELECT Table_name as TablesName from information_schema.tables where table_schema = 'test'; Output with the name of the three tables. +--------------------+ | TablesName ...
Read MoreHow to prepend a string to a column value in MySQL?
To prepend a string to a column value in MySQL, we can use the function CONCAT. The CONCAT function can be used with UPDATE statement. Creating a table. mysql> create table PrependStringOnCOlumnName -> ( -> Id int, -> Name varchar(200) -> ); Query OK, 0 rows affected (1.35 sec) Inserting some records. mysql> insert into PrependStringOnCOlumnName values(1, 'John'); Query OK, 1 row affected (0.12 sec) mysql> insert into PrependStringOnCOlumnName values(2, 'Carol'); Query OK, 1 row affected (0.18 sec) mysql> insert into PrependStringOnCOlumnName values(3, 'Johnson'); Query OK, 1 row affected (0.45 sec) ...
Read MoreHow should I enable LOAD DATA LOCAL INFILE in my.cnf in MySQL?
We can enable it with the help of the SET command with GLOBAL. The first time, local infile will be off. The following is the syntax. mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile'; Here is the output. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | local_infile | OFF | +---------------+-------+ 1 row in set (0.01 sec) We can enable the local infile with the help of ON or boolean value true or numeric value 1. The following is the syntax to enable the local infile. mysql> SET GLOBAL local_infile = 'ON'; Query OK, 0 ...
Read MorePassing an array to a query using WHERE clause in MySQL?
We can pass an array with the help of where IN clause. Let us first create a new table for our example. mysql> create table PassingAnArrayDemo -> ( -> id int, -> Firstname varchar(100) -> ); Query OK, 0 rows affected (1.60 sec) Let us now insert records. mysql> insert into PassingAnArrayDemo values(1, 'John'), (2, 'Carol'), (3, 'Smith'), (4, 'Bob'), (5, 'Johnson'), (6, 'David'), (7, 'Sam'), (8, 'Jessica'); Query OK, 8 rows affected (0.32 sec) Records: 8 Duplicates: 0 Warnings: 0 To display all records. mysql> select *from PassingAnArrayDemo; The ...
Read MoreWhat data type to use for hashed password field in MySQL?
The hashed password data type depends upon which hashing algorithm we are using. The hashing algorithm does not depends upon the input size because it produces a result of the same length. It gives the result in a series of hexadecimal digits, and we can reduce the hexadecimal digits by half with the help of UNHEX() function. There are various algorithms and data types to store values. MD5 − It can use char(32) or BINARY(16). SHA-1 − It can use data type char(40) or BINARY(20). Example of MD5 The following is an example − mysql> select MD5('This ...
Read MoreHow to install Python MySQLdb module using pip?
To install python MySQLdb module, we need to install Python current version i.e. 3.7 We need to find the location of Python Scripts where pip command is located. First, open the cmd and reach the location of Python Scripts. To open cmd, press “Windows+R” and type cmd. Here is the snapshot − Now reach where scripts are located. We will now install “MySQLdb” module. The steps are displayed in the following screenshot.
Read More