Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Восстановить таблицы из information_schema / 19 сообщений из 19, страница 1 из 1
27.09.2013, 00:09:18
    #38409118
маXон
маXон
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Восстановить таблицы из information_schema
Все доброго времени суток.

Проблема в следующем - удалили базу и осталась только её структура в information_schema.

Задача - восстановить все таблицы и их "PRIMARY KEY", "KEY" с помощью information_schema.

Удалось найти только такой SELECT:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
SELECT CONCAT(
      '   `',
      CAST(COLUMN_NAME AS CHAR),
      '` ',
      CAST(COLUMN_TYPE AS CHAR),
      IF(ISNULL(CHARACTER_SET_NAME),
         '',
         CONCAT(' CHARACTER SET ', CHARACTER_SET_NAME)),
      IF(ISNULL(COLLATION_NAME), '', CONCAT(' COLLATE ', COLLATION_NAME)),
      ' ',
      IF(IS_NULLABLE = 'NO', 'NOT NULL', ''),
      IF(IS_NULLABLE = 'NO' AND ISNULL(COLUMN_DEFAULT),
         ', ',
         CONCAT('DEFAULT ', QUOTE(COLUMN_DEFAULT), ',')),
      UPPER(extra))
      AS column_definition
 FROM INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_SCHEMA = 'база'
   AND TABLE_NAME = 'таблица'


В MySQL мало понимаю и доработать запрос не под силу. Буду благодарен тому кто поможет доработать запрос (или php скрипт), чтобы он сразу выводил готовую таблицу на подобии:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
CREATE TABLE IF NOT EXISTS `users` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_login` varchar(60) NOT NULL DEFAULT '',
  `user_pass` varchar(64) NOT NULL DEFAULT '',
  `user_nicename` varchar(50) NOT NULL DEFAULT '',
  `user_email` varchar(100) NOT NULL DEFAULT '',
  `user_url` varchar(100) NOT NULL DEFAULT '',
  `user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_activation_key` varchar(60) NOT NULL DEFAULT '',
  `user_status` int(11) NOT NULL DEFAULT '0',
  `display_name` varchar(250) NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  KEY `user_login_key` (`user_login`),
  KEY `user_nicename` (`user_nicename`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.09.2013, 05:55:03
    #38409186
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Восстановить таблицы из information_schema
маXон, для одной таблицы - вот
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
select concat(
 'create table '
 ,' таблица ('
 ,group_concat(column_definition order by rn separator ',')
 ,') engine='
 ,(select `engine` from information_schema.tables where TABLE_SCHEMA = 'база' AND TABLE_NAME = 'таблица')
 ,';'
)
from (
 SELECT ordinal_position as rn,CONCAT(
      '   `',
      CAST(COLUMN_NAME AS CHAR),
      '` ',
      CAST(COLUMN_TYPE AS CHAR),
      IF(ISNULL(CHARACTER_SET_NAME),
         '',
         CONCAT(' CHARACTER SET ', CHARACTER_SET_NAME)),
      IF(ISNULL(COLLATION_NAME), '', CONCAT(' COLLATE ', COLLATION_NAME)),
      ' ',
      IF(IS_NULLABLE = 'NO', 'NOT NULL', ''),
      IF(IS_NULLABLE = 'NO' AND ISNULL(COLUMN_DEFAULT),
         '',
         CONCAT(' DEFAULT ', QUOTE(COLUMN_DEFAULT))),
      ' ',UPPER(extra))
      AS column_definition
 FROM INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_SCHEMA = 'база'
   AND TABLE_NAME = 'таблица'
) t0

а для всей базы дописывать влом :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.09.2013, 07:31:49
    #38409201
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Восстановить таблицы из information_schema
Держите :)
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
SET @@session.group_concat_max_len = 100500; /*не меньше, чем предполагаемый размер итогового скрипта создания базы*/
set @_schema:='название_базы';

select group_concat(ctf order by tn separator ';\r\n\r\n') da_script
from (

 select 
  t_columns.tn
  ,concat('create table '
          ,' `',t_columns.tn,'` (\r\n '
          ,group_concat(t_columns.col_def order by rn separator '\r\n ,')
          ,if(isnull(t_pkeys.pkey),'',concat('\r\n ,PRIMARY KEY (',t_pkeys.pkey,')'))
          ,'\r\n) engine='
          ,t_engines.`engine`
 ) as ctf

 from (
  SELECT table_name tn, ordinal_position as rn
  ,CONCAT('`',CAST(COLUMN_NAME AS CHAR),'` '
         ,CAST(COLUMN_TYPE AS CHAR)
          ,IF(ISNULL(CHARACTER_SET_NAME),'',CONCAT(' CHARACTER SET ', CHARACTER_SET_NAME))
 		,IF(ISNULL(COLLATION_NAME), '', CONCAT(' COLLATE ', COLLATION_NAME))
 		,' '
 		,IF(IS_NULLABLE = 'NO', 'NOT NULL', '')
 		,IF(IS_NULLABLE = 'NO' AND ISNULL(COLUMN_DEFAULT)
 		    ,''
 			,CONCAT(' DEFAULT '
 			        ,if(COLUMN_DEFAULT like 'CURRENT_TIMESTAMP%'
 					    ,COLUMN_DEFAULT
 						,QUOTE(COLUMN_DEFAULT)))
 		   )
          ,' ',UPPER(extra))
       AS col_def
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE  TABLE_SCHEMA = @_schema
 ) t_columns

 join (
  select table_name tn,`engine`
  from information_schema.tables 
  where TABLE_SCHEMA = @_schema
 ) t_engines on t_columns.tn=t_engines.tn

 left join (
  SELECT table_name tn
  ,group_concat(if(ifnull(column_key,'')<>'PRI'
                ,null
				,concat('`',column_name,'`')) separator ','
			   ) pkey
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE  TABLE_SCHEMA = @_schema
  group by tn
 ) t_pkeys on t_columns.tn=t_pkeys.tn

group by t_columns.tn

) t0

Ну а индексы уже как-нибудь сами...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.09.2013, 15:04:03
    #38409982
маXон
маXон
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Восстановить таблицы из information_schema
tanglir, спасибо что помогаете.

Но, к сожалению, результат обработки первого запроса (отдает не полную таблицу):

Код: sql
1.
create table  таблица (   `userid` int(10) unsigned NOT NULL AUTO_INCREMENT,   `usergroupid` smallint(5) unsigned NOT NULL DEFAULT '0' ,   `membergroupids` char(250) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL DEFAULT '' ,   `displaygroupid` smallint(5) unsigned NOT NULL DEFAULT '0' ,   `username` varchar(100) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL DEFAULT '' ,   `password` char(32) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL DEFAULT '' ,   `passworddate` date NOT NULL DEFAULT '0000-00-00' ,   `email` char(100) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL DEFAULT '' ,   `styleid` smallint(5) unsigned NOT NULL DEFAULT '0' ,   `parentemail` char(50) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL DEFAULT '' ,   `homepage` char(100) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL DEFAULT '' ,   `icq` char(20) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL DEFAULT '' ,   `aim` char(20) CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL DEFAULT '' ,   `yah) engine=MyISAM;



а второй запрос вообще не работает (session.group_concat_max_len менял, толку мало), выдает много цифр, если нажать "Полные тексты" то:
Код: sql
1.
NULL
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.09.2013, 16:08:13
    #38410103
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Восстановить таблицы из information_schema
маXон, вы название базы хоть подставили какое надо? у меня и первый, и второй прекрасно на двух БД отработали... мускль 5.0.не_помню_сколько, а у вас какая версия? (впрочем, навряд ли это имеет отношение к делу)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.09.2013, 16:10:51
    #38410112
маXон
маXон
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Восстановить таблицы из information_schema
tanglir, конечно.
Версия MySQL-клиента: 5.0.22
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.09.2013, 16:16:12
    #38410122
маXон
маXон
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Восстановить таблицы из information_schema
Версия MySQL сервера: 5.0.18-nt-max
Выполняю через phpMyAdmin 3.4.10.1
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.09.2013, 16:21:16
    #38410136
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Восстановить таблицы из information_schema
маXон, ну хз. Вот этот кусок
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
  SELECT table_name tn, ordinal_position as rn
  ,CONCAT('`',CAST(COLUMN_NAME AS CHAR),'` '
         ,CAST(COLUMN_TYPE AS CHAR)
          ,IF(ISNULL(CHARACTER_SET_NAME),'',CONCAT(' CHARACTER SET ', CHARACTER_SET_NAME))
 		,IF(ISNULL(COLLATION_NAME), '', CONCAT(' COLLATE ', COLLATION_NAME))
 		,' '
 		,IF(IS_NULLABLE = 'NO', 'NOT NULL', '')
 		,IF(IS_NULLABLE = 'NO' AND ISNULL(COLUMN_DEFAULT)
 		    ,''
 			,CONCAT(' DEFAULT '
 			        ,if(COLUMN_DEFAULT like 'CURRENT_TIMESTAMP%'
 					    ,COLUMN_DEFAULT
 						,QUOTE(COLUMN_DEFAULT)))
 		   )
          ,' ',UPPER(extra))
       AS col_def
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE  TABLE_SCHEMA = @_schema

тоже ничего не выдаёт?
PS. Ешкин кот, как форматирование сбилось-то...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.09.2013, 16:30:52
    #38410151
маXон
маXон
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Восстановить таблицы из information_schema
tanglir, все ок, вывело все таблицы и столбцы.

 
результат
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.09.2013, 17:46:29
    #38410259
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Восстановить таблицы из information_schema
маXон,

ну а второй селект? тот, что к information_schema.tables?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.09.2013, 18:15:12
    #38410286
маXон
маXон
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Восстановить таблицы из information_schema
tanglirмаXон,

ну а второй селект? тот, что к information_schema.tables?

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
select concat(
 'create table '
 ,' таблица ('
 ,group_concat(column_definition order by rn separator ',')
 ,') engine='
 ,(select `engine` from information_schema.tables where TABLE_SCHEMA = 'база' AND TABLE_NAME = 'таблица')
 ,';'
)
from (
 SELECT ordinal_position as rn,CONCAT(
      '   `',
      CAST(COLUMN_NAME AS CHAR),
      '` ',
      CAST(COLUMN_TYPE AS CHAR),
      IF(ISNULL(CHARACTER_SET_NAME),
         '',
         CONCAT(' CHARACTER SET ', CHARACTER_SET_NAME)),
      IF(ISNULL(COLLATION_NAME), '', CONCAT(' COLLATE ', COLLATION_NAME)),
      ' ',
      IF(IS_NULLABLE = 'NO', 'NOT NULL', ''),
      IF(IS_NULLABLE = 'NO' AND ISNULL(COLUMN_DEFAULT),
         '',
         CONCAT(' DEFAULT ', QUOTE(COLUMN_DEFAULT))),
      ' ',UPPER(extra))
      AS column_definition
 FROM INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_SCHEMA = 'база'
   AND TABLE_NAME = 'таблица'
) t0



Этот?

Он отдает не все поля:

Код: sql
1.
create table  таблица (   `userid` int(10) unsigned NOT NULL AUTO_INCREMENT,   `usergroupid` smallint(5) unsigned NOT NULL DEFAULT '0' ,   `membergroupids` varchar(250) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '' ,   `displaygroupid` smallint(5) unsigned NOT NULL DEFAULT '0' ,   `username` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '' ,   `password` varchar(32) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '' ,   `passworddate` date NOT NULL DEFAULT '0000-00-00' ,   `email` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '' ,   `styleid` smallint(5) unsigned NOT NULL DEFAULT '0' ,   `parentemail` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '' ,   `homepage` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '' ,   `icq` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '' ,   `aim` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NUL) engine=MyISAM;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.09.2013, 18:41:26
    #38410319
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Восстановить таблицы из information_schema
tanglir
Код: sql
1.
2.
3.
  select table_name tn,`engine`
  from information_schema.tables 
  where TABLE_SCHEMA = @_schema

этот
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.09.2013, 19:10:29
    #38410352
маXон
маXон
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Восстановить таблицы из information_schema
tanglirtanglir
Код: sql
1.
2.
3.
  select table_name tn,`engine`
  from information_schema.tables 
  where TABLE_SCHEMA = @_schema

этот
Код: sql
1.
2.
3.
4.
5.
6.
7.
tn 	        engine
access 	        MyISAM
adminhelp 	MyISAM
administrator 	MyISAM
adminlog 	MyISAM
adminmessage 	MyISAM
adminutil 	MyISAM


и т.д.
В общем отдает все таблицы.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.09.2013, 10:32:53
    #38410602
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Восстановить таблицы из information_schema
маXон, ну тогда я не знаю. У меня всё работает. И судя по представленным вами данным, и у вас работать должно. Попробуйте ещё без этих двух строчек
Код: sql
1.
2.
select group_concat(ctf order by tn separator ';\r\n\r\n') da_script
from (

и без последней. Должно вывести список таблиц с криэйтами...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.09.2013, 17:21:45
    #38410733
маXон
маXон
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Восстановить таблицы из information_schema
tanglir,

запрос:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
SET @@session.group_concat_max_len = 100500;
set @_schema:='база';


 select 
  t_columns.tn
  ,concat('create table '
          ,' `',t_columns.tn,'` (\r\n '
          ,group_concat(t_columns.col_def order by rn separator '\r\n ,')
          ,if(isnull(t_pkeys.pkey),'',concat('\r\n ,PRIMARY KEY (',t_pkeys.pkey,')'))
          ,'\r\n) engine='
          ,t_engines.`engine`
 ) as ctf

 from (
  SELECT table_name tn, ordinal_position as rn
  ,CONCAT('`',CAST(COLUMN_NAME AS CHAR),'` '
         ,CAST(COLUMN_TYPE AS CHAR)
          ,IF(ISNULL(CHARACTER_SET_NAME),'',CONCAT(' CHARACTER SET ', CHARACTER_SET_NAME))
 		,IF(ISNULL(COLLATION_NAME), '', CONCAT(' COLLATE ', COLLATION_NAME))
 		,' '
 		,IF(IS_NULLABLE = 'NO', 'NOT NULL', '')
 		,IF(IS_NULLABLE = 'NO' AND ISNULL(COLUMN_DEFAULT)
 		    ,''
 			,CONCAT(' DEFAULT '
 			        ,if(COLUMN_DEFAULT like 'CURRENT_TIMESTAMP%'
 					    ,COLUMN_DEFAULT
 						,QUOTE(COLUMN_DEFAULT)))
 		   )
          ,' ',UPPER(extra))
       AS col_def
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE  TABLE_SCHEMA = @_schema
 ) t_columns

 join (
  select table_name tn,`engine`
  from information_schema.tables 
  where TABLE_SCHEMA = @_schema
 ) t_engines on t_columns.tn=t_engines.tn

 left join (
  SELECT table_name tn
  ,group_concat(if(ifnull(column_key,'')<>'PRI'
                ,null
				,concat('`',column_name,'`')) separator ','
			   ) pkey
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE  TABLE_SCHEMA = @_schema
  group by tn
 ) t_pkeys on t_columns.tn=t_pkeys.tn

group by t_columns.tn



 
ответ:
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.09.2013, 17:47:30
    #38410739
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Восстановить таблицы из information_schema
маXон,
"63726561746520" - это "create " в хексах. Разбирайтесь, почему так происходит :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.09.2013, 17:53:57
    #38410743
маXон
маXон
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Восстановить таблицы из information_schema
tanglir,
откуда мне знать. Может Вы что-то подскажете?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.09.2013, 18:16:21
    #38410761
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Восстановить таблицы из information_schema
маXон, определённо сказать невозможно. ХШ говорит, что пхпадмин может чудить. Попробуйте выполнить запрос из клиента mysql. Или из devart dbforge, или из heidisql...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.09.2013, 23:38:53
    #38410894
маXон
маXон
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Восстановить таблицы из information_schema
tanglirмаXон, определённо сказать невозможно. ХШ говорит, что пхпадмин может чудить. Попробуйте выполнить запрос из клиента mysql. Или из devart dbforge, или из heidisql...Действительно, взял клиент mysql и получил нормальный вывод данных.
Большое спасибо за помощь.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Восстановить таблицы из information_schema / 19 сообщений из 19, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 322ms
Закрыть
start [/forum/topic.php?fid=47&mobile=1&tid=1835961]:
 0ms
get settings:
 7ms
get forum list:
 10ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 43ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 31ms
get tp. blocked users:
 1ms
others: 217ms
total:  322ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]